SQL Injection Attacks explained for the Developer

SQL injection attacks have become the most widely exploited security attacks on the Internet as they can usually bypass layers of security such as firewalls and any other network detection sensors. They are used most often to attack databases and for extracting any confidential information such as Social Security Numbers, Credit Card information etc. According to the Verizon Security report, in 2008 SQL Injection attacks ranked first when using to compromise databases - a staggering 79% of the 285 million records stolen.  What is a SQL Injection Attack? SQL Injections happen when some application takes in content from the user and uses that data to construct a SQL statement without validating or sanitizing that content. For example, let us take a sample SQL query SELECT * FROM customers WHERE PolicyNum = '12345'; Now, if this query is not properly sanitized, then a malicious attacker can use it to execute arbitrary SQL statements such as SELECT * FROM customers WHERE PolicyNum = '12345'; DROP TABLE customers --' In the above statement, the '(quotation mark) character terminates the string literal in the SQL statement. The ;(semicolon) indicates it is the end of the current statement. The --(double hash) tells SQL to ignore the rest of the text. In this case, the ' character is ignored, which if not would cause a SQL parser error. How to write Secure Code to prevent SQL Injection attacks? Applications invoke interpreters, including SQL or LDAP. These interpreters take commands and data and execute the instructions. Injection happens when user input crosses the line between code and data where an attacker sends malicious data or commands into the application, tricking it into behaving differently. Attackers could modify queries to gain access to unauthorized information or corrupt the data store.

• Validate input to verify user data cannot modify the meaning of commands and database queries.
• The account used to access the database must have the minimum amount of privileges required by the application. Do not use an administrator account. Enforce least privilege when connecting to databases and other backend systems.
• Use stored procedures and parameterized queries to bind all supplied variables in the SQL query. The results of the query must match what was expected.
• A secured file system is a good alternative to store database credentials.

Parameterized Queries: Validate all parameters carefully to ensure they cannot modify the query. Treat all input variables as data only (i.e. bind all variables).

• String concatenation must not be used to build SQL queries.
• Treat embedded quotes etc as simple characters and not SQL and the input parameter value as mere data.
• Use strongly typed parameterized queries, such as SqlCommand with SqlParameter or an Object Relational Mapping (ORM) technique like Hibernate in .NET.

Stored Procedures: Applications pass parameters to stored procedures. Stored procedures return result sets and/or output parameters. By using stored procedures, SQL expertise is moved to the database and developers no longer have to generate dynamic SQL in their code.

• .NET: Use SqlCommand with CommandType.StoredProcedure
• ASP: Use Server.CreateObject with command type: adCmdStoredProc

Connection Strings: A connection string or URL contains the attributes required for an application to access a database. Anyone with access to code or configuration files where connection strings are usually stored, and network access to the database(insider threat) can use credentials to attack the database or steal information.

• Encrypt connection strings and store them in the registry on Windows platform using aspnet_setreg.exe.
• Use the DPAPI (Data Protection API) in the .NET framework to encrypt/decrypt connection strings from web.config file.
• Limited database account: The account used to access the database must have the minimum amount of privilege required by the application. Only grant execute permissions to specific stored procedures in the database and provide no direct table access. This limits the damage that an attacker can do if an attack successfully reaches the database.

Direct object references: Do not expose internal object references (ids) to users. This could be in the form of hidden fields or other form parameters in the URL. A malicious attacker can manipulate these references and possibly access records outside of their authorization scope for the next request. Database references commonly exposed include Primary/foreign keys, column names, and table names.

• Use restrictions in “where” clause to enforce access control. Such restrictions ensure ‘expected’ relationships remain true such as the current user is owner of referenced account.

Example: A Quote table with quoteID primary key and userID foreign key

SELECT * FROM quote WHERE quoteID = {current quote id} is dangerous SELECT * FROM quote WHERE quoteID = {current quote id} AND userID ={current user id} limits queries to current user where {current user id} is coming from the session.

The results of the query must match what was expected. If a single record was expected, then ensure that only one record was obtained. Validate results returned from database to see if they match, check for error codes, and handle any exceptions.