Newbie Programmer VS SQL Injection
We newbie programmers have it hard enough - remembering which language requires a semicolon at the end of statements, panicking to reverse a tragic git 'commit', and referring to the Ruby Doc for Array.map! for the hundreth time. So what's the harm in adding one more malicious web battle you have to learn to fight while programming?
Many webpages allow users to input their own search values, and in this is a potentially deleterious threat called SQL Injection - "a technique where malicious users can inject SQL commands into an SQL statement via webpage input". Newbie programmers, like myself, might be thinking, "What?" So, here's a simple example.
Provided a field for entering a userID on any site, if there's no front-end validations to prevent users from entering "wrong" input, they can enter a statement such as 105 or 1=1 in the field. If this is input into a SQL statement such as SELECT * FROM Users WHERE UserID = 105 or 1=1, all rows of the table User will be returned since WHERE 1=1 is always true. The danger of this? If the User table contains passwords and personal information, a hacker may gain access to it all!
If that wasn't frightening enough, here's another example with some "comic"al relief. Imagine the same ubiquitous userID field on a site, except this time we input 105; DROP TABLE Suppliers. If the connected database supports batched SQL statements (multiple statements separated by a semicolon), the resulting SQL statement will be SELECT * FROM Users WHERE UserID = 105; DROP TABLE Suppliers. Uh-oh, we lose all our data in the table Suppliers then!
Protection
Alright, so I've worried you enough with SQL injections, what are some potential means of defense?
- Utilize front-end validation to ensure user input adheres to business requirements and to ensure the sifting out of suspicious input.
- Use SQL parameters to build queries rather than building SQL statements dynamically using string concatenation.
The first defense just means popping up error messages or prohibiting submission of data if there is clearly something fishy in the input. For example, some sites may not allow special characters in userIDs. The second involves parameterized queries. As opposed to dynamic queries, parameterized queries use a parameter instead of injecting values directing into commands. This approach allows the database to distinguish between code and data. So, in our first example, if an attacker entered 105 or 1=1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string 105 or 1=1.
A high-level warning, this post surely doesn't begin to cover all the ground regarding the potential threats and defense measures against SQL Injection. But awareness is the first step, and I hope y'all - newbie programmers and all - gained even the slightest insight into the web warfare of hackers.
