SQL injection a technique by which some code is injected to exploit the security vulnerability found in the application layer of the database. This is a common attack in login based website. This mostly occurs when the user input is incorrectly filtered or due to incorrect type handling.

» Incorrectly filtered user input

This attack is possible when user input is not checked for escape characters and passed into SQL statement. The statement therefore results in a complex statement to what the programmer had planned  to, thereby revealing much more data or allowing access to the parts of the database which were not supposed to be exposed.

Eg: The following line of code takes user input as (username and password) check the entry and then do some query execution.

1

SELECT * from `users` WHERE `username`="+username+" AND `password`= "+password ";

However this query can be vulnerable to SQL injection by setting the username variable as  (a’ or  ‘a’='a ) and password variable as (a’ or ‘a’='a).

This query allows the access because  ‘a’ = ‘a’  part makes the statement always true.

While most SQL server implementations allow multiple statements to be executed with one call, some SQL APIs such as php’s mysql_query do not allow this for security reasons. This prevents hackers from injecting entirely separate queries, but doesn’t stop them from modifying queries.

Eg: The previous query can me modified and made more dangerous by changing it to

1

a';DROP TABLE users; SELECT * FROM data WHERE 'a' = 'a

» Incorrect type handling

This server is vulnerable to this kind of attack when the user given input field is not strongly type checked.

Eg: In the statement

1

"SELECT * FROM data WHERE id = " + a_variable + ";"

a_variable can be replaced by  1;DROP TABLE users there by deletion of the table.

» Preventing SQL injection

• Use Double Quotes: Replace all the single quotes that your users’ input contains with double quotes. This simple precaution will go a long way towards warding off SQL-injection attacks. Single quotes often terminate SQL expressions and give the input more power than is necessary. Replacing the single quotes with double quotes will cause many SQL-injection attacks to fail.
• Validate All Input: Before using user input data in the SQL query validate all the user input data i.e. numbers should be validated for numbers strings for strings character etc.This prevents the user from entering codes to database.
• Never Connect with the Administrator Account: The user which connects to the database in the script should have least privileges so that he should not be able to access all the functionalities of the database.
• Use of latest versions: Always use of the latest versions of your interpreters and softwares like  PHP 5 and .NET 2 or 3(aspx), most SQL injection do not work because all single quotes and double quotes, which are given as the input by user are replaced by an escape characters followed by the quote ( i.e ‘ is replaced by \’ and ” is replaced by \”) preventing the SQL injection. However ASP and older version of PHP and JDBC API are vulnerable to it.
• Use parametrized platforms: Parametrized statements can be used that work in various platforms
  1. In JDBC:
     ^?View Code JAVA

     1 2 3

     APIPreparedStatement prep = conn.prepareStatement("SELECT * FROM USERS WHERE USERNAME=? AND PASSWORD=?"); prep.setString(1, username); prep.setString(2, password);

  2. In C#:
     ^?View Code CSHARP

     1 2 3 4 5 6 7 8

     (SqlCommand myCommand = new SqlCommand("SELECT * FROM USERS WHERE USERNAME=@username AND PASSWORD=HASHBYTES('SHA1', @password)", myConnection)) { myCommand.Parameters.AddWithValue("@username", user); myCommand.Parameters.AddWithValue("@password", pass); myConnection.Open(); SqlDataReader myReader = myCommand.ExecuteReader()); ..................... }

  3. In PHP version 5 and above:
     ^?View Code PHP

     1 2 3 4 5

     $db = new PDO('pgsql:dbname=database'); $stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password"); $stmt->bindParam(':username', $user); $stmt->bindParam(':password', $pass); $stmt->execute();

  4. In  ColdFusion:
     ^?View Code ACTIONSCRIPT

     1 2 3 4 5

     <cfquery name="Recordset1" datasource="cafetownsend"> SELECT * FROM COMMENTS WHERE COMMENT_ID =<cfqueryparam value="#URL.COMMENT_ID#" cfsqltype="cf_sql_numeric"></cfqueryparam> </cfquery>

• Enforcements at the coding level: Using object-relational mapping (ORMs) libraries avoids the need to write SQL code. The ORM library in effect will generate parametrized SQL statements from object-oriented code.

Tags: Best Practices, Code injection, Computer security, Data management, Databases, Microsoft SQL Server, php, Query languages, SQL, SQL injection, Technology/Internet

This entry was posted on Sunday, October 18th, 2009 at 11:03 am and is filed under Database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.