How to Hack the Planet

August 26, 2014

When SQL is used to display data on a web page, it is common to let users input their own search values. Since SQL statements are text only, it is easy to dynamically change SQL statements to provide the user with selected data. Thus, there are potential dangers with using user input in SQL statements.

What is SQL Injection?

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement via web page input. Injected SQL commands can alter SQL statements and compromise the security of a web application.

SQL Injection Based on 1=1 is Always True

The example below creates a SELECT statement by adding a variable (txtUserId) to a SELECT string. The variable is fetched from user input.


			txtUserId = getRequestString("UserId");
			txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

Let's say that the original purpose of the code was to create an SQL statement to select a user with a given user id. There is nothing to prevent a user from entering "wrong" input, so the user could enter something like '105 or 1=1'. The resulting SELECT statement would be:


			SELECT * FROM Users WHERE UserId = 105 or 1=1

The SQL code above is valid. It will return all rows from the table Users, since WHERE 1=1 is always true. Obviously, this is dangerous. What if the Users table contained user names and passwords? A hacker might then get access to all the user names and passwords in the database by simply inserting 105 or 1=1 into the input box when prompted.

SQL Injection Based on ""="" is Always True

Here is a common construction used to verify user login to a web site:


			userName = getRequestString("UserName");
			userPassword = getRequestString("UserPassword");

			sql = "SELECT * FROM Users WHERE Name ='" + userName + "' AND Password ='" + userPassword + "'"

A hacker might get access to user names and passwords in a database by simply inserting " or ""=" into the user name or password text box. This would create a valid SQL statement like:


			SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""

This would return all rows from the table Users, since WHERE ""="" is always true.

Primary Defenses

Some web developers use a "blacklist" of words or characters to search for in SQL input in order to prevent SQL injection attacks, but this is not a good idea. Many of these words (like delete or drop) and characters (like semicolons and quotation marks) are used in common language, and should be allowed in many types of input.

Prepared Statements (Parameterized Queries): Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied. Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. An example of a prepared statement in Ruby is:


			insert_new_user = db.prepare "INSERT INTO users (name, age, gender) VALUES (?, ? ,?)"
			insert_new_user.execute 'John Smith', '20', 'male'

Stored Procedures: Stored procedures have the same effect as the use of prepared statements when implemented safely (don't include any unsafe dynamic SQL generation). Stored procedures require the developer to define the SQL code first, and then pass in the parameters after. The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is defined and stored in the database itself, and then called from the application. Both of these techniques have the same effectiveness in preventing SQL injection so you should choose which approach makes the most sense for you. An example of stored procedures in Ruby is:


			PROCEDURE SafeGetBalanceQuery(
   			   @UserID varchar(20),
   			   @Dept varchar(10)) AS BEGIN
 
   			   SELECT balance FROM accounts_table WHERE user_ID = @UserID AND department = @Dept
 			END

Here, no dynamic SQL is being created. Parameters passed into stored procedures are naturally bound to their location within the query without anything special being required.

Escaping All User Supplied Input: The third technique is to escape user input before putting it in a query. If you are concerned that rewriting your dynamic queries as prepared statements or stored procedures might break your application or adversely affect performance, then this might be the best approach. However, this methodology is frail compared to using parameterized queries and should only be used with caution to retrofit source code that relates to a no-longer supportedor manufactured operating system or other computer technology (aka legacy code). Applications built from scratch, or applications requiring low-risk tolerance should be built or re-written using parameterized queries. This technique works like this. Each database management system (DBMS) supports one or more character escaping schemes specific to certain kinds of queries. If you escape all user supplied input using the proper escaping scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.

For example, MySQL supports two escaping modes: (1) ANSI_QUOTES SQL mode, which simply encodes all ' (single tick) characters with '' (two single ticks); and (2) MySQL mode, which does the following:


 			NUL (0x00) --> \0  [This is a zero, not the letter O]
 			BS  (0x08) --> \b
 			TAB (0x09) --> \t
 			LF  (0x0a) --> \n
 			CR  (0x0d) --> \r
 			SUB (0x1a) --> \Z
 			"   (0x22) --> \"
 			%   (0x25) --> \%
 			'   (0x27) --> \'
 			\   (0x5c) --> \\
 			_   (0x5f) --> \_ 
 			all other non-alphanumeric characters with ASCII values less than 256  --> \c
 			where 'c' is the original non-alphanumeric character.

Additional Defenses

Beyond adopting one of the three above primary defenses, it is also recommended to adopt both of the following additional defenses:

Least Privilege: To minimize the potential damage of a successful SQL injection attack, you should minimize the privileges assigned to every database account in your environment. Do not assign DBA or admin type access rights to your application accounts. Make sure that accounts that only need read access are only granted read access to the tables they need access to. If an account only needs access to portions of a table, consider creating a view that limits access to that portion of the data and assigning the account access to the view instead, rather than the underlying table. Rarely, if ever, grant create or delete access to database accounts.

White List Input Validation: Input validation can be used to detect unauthorized input before it is passed to the SQL query. (See the Input Validation Cheat Sheet.)

And for more information, check out the SQL Injection Cheat Sheet.