Understanding SQL Injection- Examples .

SQL injection is a hacking technique which attempts to pass SQL commands through a web application for execution by a database.

SQL Injection arises because the fields available for user input allow SQL statements to pass through and query the database directly.

Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:


Weak input validation.
Dynamic construction of SQL statements without the use of type-safe parameters.
Use of over-privileged database logins.

Example of a SQLInjection Attack

Here is a sample basic HTML form with two inputs, login and password.

<form method="post" action="http://testasp.myweb.com/login.asp"> 
<input name="name" type="text" id="name"> 
<input name="password" type="password" id="password"> 
</form>

The easiest way for the login.asp to work is by building a database query that looks like this:

SELECT id
FROM logins
WHERE username = '$username'
AND password = '$password’

If the variables $username and $password are requested directly from the user's input, this can easily be compromised. Suppose that we gave "Joe" as a username and that the following string was provided as a password: anything' OR 'x'='x

SELECT id
FROM logins
WHERE username = 'xyz'
AND password = 'anything' OR 'x'='x'

As the inputs of the web application are not properly sanitised, the use of the single quotes has turned the WHERE SQL command into a two-component clause.

The 'x'='x' part guarantees to be true regardless of what the first part contains.

This will allow the attacker to bypass the login form without actually knowing a valid username / password combination!

Another Example

In the following example, assume that a web site is being used to mount an attack on the database. If you think about a typical SQL statement, you might think of something like:

SELECT ProductName, QuantityPerUnit, UnitPrice 
FROM Products 
WHERE ProductName LIKE '%'

The objective of the attacker is to inject their own SQL into the statement that the application will use to query the database. If, for instance, the above query was generated from a search feature on a web site, then they user may have inserted the "G" as their query. If the server side code then inserts the user input directly into the SQL statement, it might look like this:

string sql = "SELECT ProductName, QuantityPerUnit, UnitPrice "+
    "FROM Products " +
    "WHERE ProductName LIKE '"+this.search.Text+"%';
SqlDataAdapter da = new SqlDataAdapter(sql, DbCommand);
da.Fill(productDataSet);

This is all fine if the data is valid, but what if the user types something unexpected? What happens if the user types:

' UNION SELECT name, type, id FROM sysobjects;--

Note the initial apostrophe; it closes the opening quote in the original SQL statement. Also, note the two dashes at the end; that starts a comment, which means that anything left in the original SQL statement is ignored.

Now, when the attacker views the page that was meant to list the products the user has searched for, they get a list of all the names of all the objects in the database and the type of object that they are. From this list, the attacker can see that there is a table called Users. If they take note of the id for the Users table, they could then inject the following:

' UNION SELECT name, '', length FROM syscolumns 
WHERE id = 1845581613;--

This would give them a list of the column names in the Users table. Now they have enough information to get access to a list of users, passwords, and if they have admin privileges on the web site.

' UNION SELECT UserName, Password, IsAdmin FROM Users;--

Assume that there is a table called Users which has columns called UserName and Password, it is possible to union that with the original query and the results will be interpreted as if the UserName was the name of the product and the Password was the quantity per unit. Finally, because the attacker discovered that there is a IsAdmin column, they are likely to retrieve the information in that too.


Let us go to next page for Example 3.

Another Example

Consider what happens when a user types the following string in the SSN text box, which is expecting a Social Security number of the form nnn-nn-nnnn.

' ; DROP DATABASE subs  --

  

Using the input, the application executes the following dynamic SQL statement or stored procedure, which internally executes a similar SQL statement.

// Use dynamic SQL

SqlDataAdapter myCommand = new SqlDataAdapter(

          "SELECT lname, fname FROM authors WHERE id = '" + 

          SSN.Text + "'", myConnection);

// Use stored procedures

SqlDataAdapter myCommand = new SqlDataAdapter(

                                "LoginStoredProcedure '" + 

                                 SSN.Text + "'", myConnection);

  

The developer's intention was that when the code runs, it inserts the user's input and generates a SQL the following statement.

SELECT lname, fname FROM authors WHERE au_id = '272-32-9999'

  

However, the code inserts the user's malicious input and generates the following query.

SELECT lname, fname FROM authors WHERE id = ''; DROP DATABASE subs --'

  

In this case, the ' (single quotation mark) character that starts the rogue input terminates the current string literal in the SQL statement. It closes the current statement only if the following parsed token does not make sense as a continuation of the current statement but does make sense as the start of a new statement. As a result, the opening single quotation mark character of the rogue input results in the following statement.

SELECT au_lname, au_fname FROM authors WHERE au_id = ''

  

The; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the following malicious SQL code.

; DROP DATABASE pubs

  

Note   The semicolon is not necessarily required to separate SQL statements. This is dependent on vendor or implementation, but Microsoft SQL Server does not require them.

 For example, SQL Server parses the following as two separate statements:

SELECT * FROM MyTable DELETE FROM MyTable

Finally, the -- (double dash) sequence of characters is a SQL comment that tells SQL to ignore the rest of the text. In this case, SQL ignores the closing ' (single quotation mark) character, which would otherwise cause a SQL parser error.

In our next post we will explain about preventing SQL Injection