Common SQL Injection Attacks and How to Prevent Them (Option 2)

Method 1: Stacked Queries

This method involves completing one SQL statement and then writing a completely new one. For example, a website has users input a username to view information, and the SQL statement is, "SELECT * FROM Users WHERE Username='username'". If stacked queries are supported, then a user can input "'; SELECT * FROM *;" and get the contents of the entire database when SQL runs "SELECT * FROM Users WHERE Username=''; SELECT * FROM *;".

To prevent this, do not allow stacked queries if you have the option. For instance, when using mysql and php, use the statement "mysqli_query" instead of "mysqli_multi_query". The former will stop stacked queries while the latter permits them.

Method 2: Error Based Injection

This method makes use of SQL error messages that are displayed to the user. The code is somewhat complicated, but the concept is simple. A user attempts to do something that will almost certainly create an error, such as converting a table name to an integer, and then gains information from the resulting error message. Once a user knows the names of various tables, they will have a much greater ability to compromise the database.

To stop this attack, make sure that internal SQL errors are never displayed to a user. SQL errors should be handled before they make it to the user to stop users from gaining information from them.

Method 3: Boolean Based Injection

In this attack, a user appends various conditions to a conditional statement. In the example of searching a users list with the SQL code "SELECT * FROM Users WHERE Username='username'", username could be set to "myusername' AND conditional statement". If your user information is returned, then the conditional statement is true. If it isn't, then the statement is false. By performing a series of these queries, an attacking can gather a variety of information about the database.

To prevent this attack (and most SQL attacks), user input should never be run as SQL code. There are many ways to do this, with varying levels of potential vulnerability, but the safest method is to use prepared statements. This passes user input to SQL as a parameter to a prewritten statement. Doing this ensures that SQL will not interpret what the user entered as SQL code. It will be treated as whatever data type it is meant to be.