ghammell'sBlog

Amongst a handful of what some people would consider drawbacks to SQL, one major issue in particular is SQL injection (SQLI). SQLI is when a malicious user of an application imbeds SQL commands in their inputs to the application in an attempt to alter, extract, or delete information contained in the application's database. For example, instead of appropriately filling out a 'name' field within a website's form, an attacker might instead input a line of SQL code. This could enable them to access the database where their information would otherwise be stored, and where other user information currently resides.

Clearly, SQLI is an issue. The possibility that a hacker could obtain and alter company databases is a huge security risk. Without some way of preventing SQLI, personal information is essentially up for grabs, and the information that companies rely on daily is at risk of being lost. The loss of important databases could be a huge hurdle operationally for any company.

Let's take a look at an example of SQLI. There might be a field on a website that requires an 'id' input from the user, maybe to sign in to the website. The SQL that would be run upon submitting that field could look something like this:

SELECT * FROM userinfo WHERE id =" + user_input + ";

Where the variable user_input would be set equal to the id that the user input. This querie would return all the information related to a specific user. A hacker might fill out the id field with this code, as opposed to an actual id:

1;DROP TABLE users

Effectively altering the SQL querie that is run to be:

SELECT * FROM userinfo WHERE id=1;DROP TABLE users;

The hacker responsible for this attack doesn't care about any of the user information - it is unlikely that anything of meaning will be returned because the only id being toggled is id 1. However, by including the 'DROP TABLE users' code at the end, the hacker is forcing the deletion of the table that stores all the user information. This could be a huge hindrance to any company.

Pretty crazy, right? So, how can SQLI be prevented? Input constraints, that's how. By setting input constraints, you can ensure that the inputs a user is making are of the correct data type or syntax. In our example above, had we somehow constrained the input to only allow integer values, then there wouldn't have been any problem - the user input would not have been accepted. Constraints can be used to ensure user inputs are of the correct data type, length, or format, and can also be used to reject inputs with invalid characters like ';'.

Also, prepared statements are a great way to ensure that a user inputting information is unable to alter the intent of the querie. Basically, all of the SQL code is already established behind the scenes. It is not created when the user makes an input, but instead already exists. Prepared statements keep the user input seperate from the SQL code. In our example above, had we used prepared statements, the only scenario in which an error would not have been returned is if there was an actual user id with a value of '1;DROP TABLE users'.