Don't Inject My SQL

SQL databases are on one of the more popular databases used on websites. Most of the websites QW Consulting has developed use a MySQL database. (Learn more abut MySQL.) Like with any language, a SQL database can be hacked. One of the more popular hacks on the SQL databases is a SQL injection attack.

SQL injection attacks just sound bad. That is probably for a good reason since they can be pretty terrible: a SQL injection attack is where a hacker is able to alter your website's database (deleting records or even entire tables) by exploiting vulnerabilities in your website's code, specifically vulnerabilities in the way your website's code queries the database.

Thankfully SQL injection attacks are preventable - provided you have a competent web developer working on your website who pays attention to security while coding the website. At QW Consulting, every website is tested for possible SQL injection attack vulnerabilities (and other vulnerabilities too, but we'll save those for another article).

Here is how a SQL injection attack works: a hacker looks for vulnerabilities in your website's code while using your website - in the case of SQL injection attacks, the hacker is looking for vulnerabilities that let the hacker alter your database. The alterations to the database can be a minor annoyance, such as the hacker adding data to the database you do not want, or the alterations can be severe, such as deleting entire sets of data.

Here is an example: Let's say you have a form on your website to collect the name and email address of website visitors and the form wasn't developed with security in mind. Let's also say that when a visitor submits the data entered into that form, the data the user entered gets saved to a database on your website.

For the sake of this example, the visitor is a hacker and enters his name in the field as "John O'Doe" and the email as "sample@qwconsulting.com". The code in this case isn't hacker proof, so it would take the data that the visitor entered and put it directly into a query. That resulting database query would look something like:

INSERT INTO example_table SET visitor_email = 'sample@qwconsulting.com', visitor_name = 'John O'Doe';

Here is the problem: The apostrophe after the "O" in O'Doe breaks the query statement and returns an error. Apostrophes are used in database query statements to separate pieces of data so that the apostrophe after the "O" indicates to the database that "Doe" is something else. The error that gets returned would be similar to:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Doe'' at line 1

Most visitors would be confused but would not care too much if they saw this error. For the hacker, this error is pure gold. The hacker now knows you are vulnerable to an attack.

That is because this error indicates that the data entered into the form is put directly into the query and indicates that the hacker can create separate pieces of data in the database. After a little more testing of vulnerabilities, and altering the entry to expose more about the database, the hacker can alter the name entered in that form to be something like:

"John'; DROP TABLE example_table; --#"

I don't know anybody named "DROP TABLE" but if that was entered in the hypothetical form, the database query resulting from the input would look like:

INSERT INTO example_table SET visitor_email = 'sample@qwconsulting.com', visitor_name = 'John'; DROP TABLE example_table; --#'

In other words, the hacker altered the example code that inserts the form's data. The hacker's code tells the database to insert as normal, stopping after "John" and then run a new query that the hacker specified. In this case, the hacker specified a drop table query - drop table is the database way of saying delete a whole set of data. If this query were to run, your example_table, with the emails of all your visitors, would be gone.

Like I said before though, this is preventable. How?

The first solution is pretty simple: keep the database errors quiet. You want the database errors on during testing but once the site goes live, those need to be turned off or at least altered to make sure that potential hackers will only see a friendly error that doesn't expose potential website flaws. This is just a preventative measure.

The best answer, and the one I spend a lot of time working on, is to make sure that all data entered through a form needs to be checked, filtered and sanitized before getting entered into the database. In the above example this means not only making sure, among other things, that any apostrophes are changed from ' to ' instead. The slash in front of the apostrophe prevents the database query from breaking because it tells the database to ignore that apostrophe and just keep going with the statement as usual. As a result, if our would be hacker did enter in "John'; DROP TABLE example_table; --#" in the name field, the query created after sanitization would be:

INSERT INTO example_table SET visitor_email = 'sample@qwconsulting.com', visitor_name = 'John'; DROP TABLE example_table; --#'

The name stored in the database would then be "John'; DROP TABLE example_table; --#". It is a weird name but all the data in the example_table would be safe from the hacker's attempts. Sanitizing, filtering and checking data is an extra step and sadly one skipped by many developers - but never QW Consulting.

I understand potential security risks and have years of experience working on several hundreds of websites preventing SQL injection attacks (and preventing other attacks). Contact QW Consulting today and I will make sure your website is secure.

Stay Updated