5- SQL injection attacks by Anil016


More Info
									                      SQL INJECTIONS ATTACKS
SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to
communicate with a database. According to ANSI (American National Standards
Institute), it is the standard language for relational database management systems. SQL
statements are used to perform tasks such as update data on a database, or retrieve data
from a database. Some common relational database management systems that use SQL
are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database
systems use SQL, most of them also have their own additional proprietary extensions
that are usually only used on their system. However, the standard SQL commands such
as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish
almost everything that one needs to do with a database. This tutorial will provide you
with the instruction on the basics of each of these commands as well as allow you to put
them to practice using the SQL Interpreter.

1.1. What can SQL do?
      SQL can execute queries against a database
      SQL can retrieve data from a database
      SQL can insert records in a database
      SQL can update records in a database
      SQL can delete records from a database
      SQL can create new databases
      SQL can create new tables in a database
      SQL can create stored procedures in a database
      SQL can create views in a database
      SQL can set permissions on tables, procedures, and views
       To illustrate, consider a simple SQL command, SELECT. SELECT retrieves a set
of data from the database according to some criteria, using the syntax:

SELECT   list_of_column_names    from   list_of_relation_names                    where

      The list_of_relation_names may be one or more comma-separated table
      names or an expression operating on whole tables.
      The conditional_expression will contain assertions about the values of
      individual columns within individual rows in a table, and only those rows

      meeting the assertions will be selected. Conditional expressions within SQL are
      very similar to conditional expressions found in most programming languages.
     For example, to retrieve from a table called Customers all columns (designated
by the asterisk) with a value of Smith for the column Last_Name, a client program
would prepare and send this SQL statement to the server back end:

 SELECT * FROM Customers WHERE Last_Name='Smith';

The server back end may then reply with data such as this:


 | Cust_No | Last_Name | First_Name |


 | 1001      | Smith        | John         |

 | 2039      | Smith        | David        |

 | 2098      | Smith        | Matthew      |


3 rows in set (0.05 sec)

    Following is an SQL command that displays only                     two    columns,
column_name_1 and column_name_3, from the table myTable:

SELECT column_name_1, column_name_3 from myTable

     Below is a SELECT statement displaying all the columns of the table myTable2 for
each row whose column_name_3 value includes the string "brain":

SELECT * from column_name_3 where column_name_3 like '%brain%'

SQL injection is a technique used to take advantage of non-validated input
vulnerabilities to pass SQL commands through a Web application for execution by a
backend database. Attackers take advantage of the fact that programmers often chain
together SQL commands with user-provided parameters, and can therefore embed SQL
commands inside these parameters. The result is that the attacker can execute arbitrary
SQL queries and/or commands on the backend database server through the Web

2.1. Details
Databases are fundamental components of Web applications. Databases enable Web
applications to store data, preferences and content elements. Using SQL, Web
applications interact with databases to dynamically build customized data views for
each user. A common example is a Web application that manages products. In one of
the Web application's dynamic pages (such as ASP), users are able to enter a product
identifier and view the product name and description. The request sent to the database
to retrieve the product's name and description is implemented by the following SQL

SELECT ProductName, ProductDescription

FROM Products

WHERE ProductNumber = ProductNumber

     Typically, Web applications use string queries, where the string contains both the
query itself and its parameters. The string is built using server-side script languages
such as ASP, JSP and CGI, and is then sent to the database server as a single SQL
statement. The following example demonstrates an ASP code that generates a SQL

sql_query= "

SELECT ProductName, ProductDescription

FROM Products

WHERE ProductNumber = " & Request.QueryString("ProductID")

     The call Request.QueryString("ProductID") extracts the value of the Web form
variable ProductID so that it can be appended as the SELECT condition.
When a user enters the following URL:


The corresponding SQL query is executed:

SELECT ProductName, ProductDescription

FROM Products

WHERE ProductNumber = 123

     An attacker may abuse the fact that the ProductID parameter is passed to the
database without sufficient validation. The attacker can manipulate the parameter's

value to build malicious SQL statements. For example, setting the value "123 OR 1=1" to
the ProductID variable results in the following URL:

http://www.mydomain.com/products/products.asp?productid=123 or 1=1

The corresponding SQL Statement is:

SELECT ProductName, Product Description

FROM Products

WHERE ProductNumber = 123 OR 1=1

     This condition would always be true and all ProductName and
ProductDescription pairs are returned. The attacker can manipulate the application
even further by inserting malicious commands. For example, an attacker can request the
following URL:



     In this example the semicolon is used to pass the database server multiple
statements in a single execution. The second statement is "DROP TABLE Products"
which causes SQL Server to delete the entire Products table.
     An attacker may use SQL injection to retrieve data from other tables as well. This
can be done using the SQL UNION SELECT statement. The UNION SELECT statement
allows the chaining of two separate SQL SELECT queries that have nothing in common.
For example, consider the following SQL query:

SELECT ProductName, ProductDescription

FROM Products

WHERE ProductID = '123' UNION SELECT Username, Password FROM Users;

       The result of this query is a table with two columns, containing the results of the
first and second queries, respectively. An attacker may use this type of SQL injection by
requesting the following URL:

http://www.mydomain.com/products/products.asp?productid=123 UNION

SELECT user-name, password FROM USERS

     The security model used by many Web applications assumes that an SQL query is
a trusted command. This enables attackers to exploit SQL queries to circumvent access
controls, authentication and authorization checks. In some instances, SQL queries may

allow access to host operating system level commands. This can be done using stored
procedures. Stored procedures are SQL procedures usually bundled with the database
server. For example, the extended stored procedure xp_cmdshell executes operating
system commands in the context of a Microsoft SQL Server. Using the same example,
the attacker can set the value of ProductID to be "123;EXEC master..xp_cmdshell dir--",
which returns the list of files in the current directory of the SQL Server process.

Google can be the best place for searching for such vulnerable links. Now the biggest
question is how to identify that the link is vulnerable. First list the all possible links
which may or may not be vulnerable. To do this use Google dork “inurl:.php?id=”
without double quote. You can use anything in place of “id” other possibilities are
catid, sid, cid, addid, addname etc. You will get the all links which contain “.php?id=”
in their URL. One expamle of such link is given below:


     Now to identify whether this link is vulnerable or not put one single quote on the
rightmost side of the URL as shown below:


      If you get some error on the page or some data is missing on that page then this
page is vulenerable and we can attack on this website using SQL injection attack. The
reason why this method worked is that by putting a single quote at the end of the url,
we have unbalanced the SQL quary running on the backend of the page. This happened
only because of the poor knowledge of the web developer. Here developer did not filter
the single quote (‘) from the input taken through GET method.
    Another method is, If a web page accepts text entry (for example a user name and
password) then try entering a string that contains one single quote.
    A vulnerable site may behave oddly given this input. You may see an error
message such as that shown:


A simple example of SQL injection is a basic HTML form login in which you provide a
username and password:

 <form method="post" action="process_login.php">

 <input type="text" name="username">

 <input type="password" name="password">


     Given this snippet of HTML, one can deduce that the easiest (and worst) way for
the script “process_login.php” to work would be for it to build and execute a database
query that looks like this:

       "SELECT       id

       FROM logins

       WHERE username = '$username'

       and    password = '$password'";

     Under those circumstances, if the variables “$username” and “$password” are
taken directly from the user’s input, the login script can easily be tricked into believing
that a valid password has been provided by playing with the syntax of the SQL
statement. Suppose the following string were provided as the password:

      ' or '' = '

and we gave “bob” as the username. Once the variables are interpolated, the query
above would look like this:

      "SELECT       id

      FROM logins

      WHERE username = 'bob'

      and    password = '' or '' = ''";

This query will return a row because the final clause:

      ... or '' = ''

will always evaluate to true (an empty string is always equal to an empty string).

A Separate PDF File has been attached for this. Please go through the file for Learning
how to inject vulnerable web site manually.

A Separate PDF File has been attached for this. Please go through the file for SQL cheat

A Separate PDF File has been attached for this. Please go through the file for learning
how to use SQLI Helper for SQL injection attack.

To protect your application from SQL injection, perform the following steps:
Step 1. Constrain input.
Step 2. Use parameters with stored procedures.
Step 3. Use parameters with dynamic SQL.

8.1. Step 1. Constrain Input
You should validate all input to your ASP.NET applications for type, length, format,
and range. By constraining the input used in your data access queries, you can protect
your application from SQL injection.
Note: When constraining input, it is a good practice to create a list of acceptable
characters and use regular expressions to reject any characters that are not on the list.
The potential risk associated with using a list of unacceptable characters is that it is
always possible to overlook an unacceptable character when defining the list; also, an
unacceptable character can be represented in an alternate format to pass validation.

8.1.1. Constrain Input in ASP.NET Web Pages
Start by constraining input in the server-side code for your ASP.NET Web pages. Do
not rely on client-side validation because it can be easily bypassed. Use client-side
validation only to reduce round trips and to improve the user experience.
     If you use server controls, use the ASP.NET validator controls, such as the
RegularExpressionValidator and RangeValidator controls to constrain input. If you use
regular HTML input controls, use the Regex class in your server-side code to constrain
     If in the previous code example, the SSN value is captured by an ASP.NET
TextBox control, you can constrain its input by using a RegularExpressionValidator
control as shown in the following.

<%@ language="C#" %>

<form id="form1" runat="server">

 <asp:TextBox ID="SSN" runat="server"/>

 <asp:RegularExpressionValidator ID="regexpSSN" runat="server"

 ErrorMessage="Incorrect SSN Number"


 ValidationExpression="^\d{3}-\d{2}-\d{4}$" />


     If the SSN input is from another source, such as an HTML control, a query string
parameter, or a cookie, you can constrain it by using the Regex class from the
System.Text.RegularExpressions namespace. The following example assumes that the
input is obtained from a cookie.

using System.Text.RegularExpressions;

if (Regex.IsMatch(Request.Cookies["SSN"], "^\d{3}-\d{2}-\d{4}$"))


    // access the database




    // handle the bad input


     For more information about how to constrain input in your ASP.NET Web pages,
see How To: Protect From Injection Attacks in ASP.NET.

8.1.2. Constrain Input in Data Access Code
In some situations, you need to provide validation in your data access code, perhaps in
addition to your ASP.NET page-level validation. Two common situations where you
need to provide validation in your data access code are:
        Untrusted clients: If the data can come from an untrusted source or you cannot
        guarantee how well the data has been validated and constrained, add validation
        logic that constrains input to your data access routines.
        Library code: If your data access code is packaged as a library designed for use
        by multiple applications, your data access code should perform its own
        validation, because you can make no safe assumptions about the client
     The following example shows how a data access routine can validate its input
parameters by using regular expressions prior to using the parameters in a SQL

using System;

using System.Text.RegularExpressions;

public void CreateNewUserAccount(string name, string password)


    // Check name contains only lower case or upper case letters,

    // the apostrophe, a dot, or white space. Also check it is

    // between 1 and 40 characters long

    if ( !Regex.IsMatch(userIDTxt.Text, @"^[a-zA-Z'./s]{1,40}$"))

    throw new FormatException("Invalid name format");

    // Check password contains at least one digit, one lower case

    // letter, one uppercase letter, and is between 8 and 10

    // characters long

    if ( !Regex.IsMatch(passwordTxt.Text,

    @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" ))

    throw new FormatException("Invalid password format");

    // Perform data access logic (using type safe parameters)



8.2. Step 2. Use Parameters with Stored Procedures
Using stored procedures does not necessarily prevent SQL injection. The important
thing to do is use parameters with stored procedures. If you do not use parameters,
your stored procedures can be susceptible to SQL injection if they use unfiltered input
as described in the "Overview" section of this document.
     The following code shows how to use SqlParameterCollection when calling a
stored procedure.

using System.Data;

using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))


    DataSet userDataset = new DataSet();

    SqlDataAdapter myCommand = new SqlDataAdapter(

    "LoginStoredProcedure", connection);

    myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

 myCommand.SelectCommand.Parameters.Add("@au_id",                  SqlDbType.VarChar,

    myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;



     In this case, the @au_id parameter is treated as a literal value and not as executable
code. Also, the parameter is checked for type and length. In the preceding code
example, the input value cannot be longer than 11 characters. If the data does not
conform to the type or length defined by the parameter, the SqlParameter class throws
an exception.

8.2.1. Review Your Application's Use of Parameterized Stored Procedures
Because using stored procedures with parameters does not necessarily prevent SQL
injection, you should review your application's use of this type of stored procedure. For
example, the following parameterized stored procedure has several security


@var ntext


    exec sp_executesql @var


     An application that uses a stored procedure similar to the one in the preceding
code example has the following vulnerabilities:
        The stored procedure executes whatever statement is passed to it. Consider the
        @var variable being set to:

In this case, the ORDERS table will be dropped.

        The stored procedure runs with dbo privileges.
        The stored procedure's name (RunQuery) is a poor choice. If an attacker is able to
        probe the database, he or she will see the name of the stored procedure. With a
        name like RunQuery, he can guess that the stored procedure is likely to run the
        supplied query.

8.3. Step 3. Use Parameters with Dynamic SQL
If you cannot use stored procedures, you should still use parameters when constructing
dynamic SQL statements. The following code shows how to use
SqlParametersCollection with dynamic SQL.

using System.Data;

using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))


    DataSet userDataset = new DataSet();

    SqlDataAdapter myDataAdapter = new SqlDataAdapter(

    "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id",


 myCommand.SelectCommand.Parameters.Add("@au_id",                  SqlDbType.VarChar,

    myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;


Using Parameter Batching
    A common misconception is that if you concatenate several SQL statements to
send a batch of statements to the server in a single round trip, you cannot use
parameters. However, you can use this technique if you make sure that parameter

names are not repeated. You can easily do this by making sure that you use unique
parameter names during SQL text concatenation, as shown here.

using System.Data;

using System.Data.SqlClient;

. . .

using (SqlConnection connection = new SqlConnection(connectionString))


    SqlDataAdapter dataAdapter = new SqlDataAdapter(

    "SELECT CustomerID INTO #Temp1 FROM Customers " +

    "WHERE CustomerID > @custIDParm; SELECT CompanyName FROM Customers "

    "WHERE Country = @countryParm and CustomerID IN " +

    "(SELECT CustomerID FROM #Temp1);",


    SqlParameter custIDParm = dataAdapter.SelectCommand.Parameters.Add(

    "@custIDParm", SqlDbType.NChar, 5);

    custIDParm.Value = customerID.Text;

    SqlParameter countryParm = dataAdapter.SelectCommand.Parameters.Add(

    "@countryParm", SqlDbType.NVarChar, 15);

    countryParm.Value = country.Text;


    DataSet dataSet = new DataSet();



. . .

8.4. Additional Considerations
Other things to consider when you develop countermeasures to prevent SQL injection
        Use escape routines to handle special input characters.
        Use a least-privileged database account.
        Avoid disclosing error information.

8.4.1. Use Escape Routines to Handle Special Input Characters
In situations where parameterized SQL cannot be used and you are forced to use
dynamic SQL instead, you need to safeguard against input characters that have special
meaning to SQL Server (such as the single quote character). If not handled, special
characters such as the single quote character in the input can be utilized to cause SQL
Note: Special input characters pose a threat only with dynamic SQL and not when
using parameterized SQL.
     Escape routines add an escape character to characters that have special meaning to
SQL Server, thereby making them harmless. This is illustrated in the following code

private string SafeSqlLiteral(string inputSQL)


    return inputSQL.Replace("'", "''");


8.4.2. Use a Least-Privileged Database Account
Your application should connect to the database by using a least-privileged account. If
you use Windows authentication to connect, the Windows account should be least-
privileged from an operating system perspective and should have limited privileges
and limited ability to access Windows resources. Additionally, whether or not you use
Windows authentication or SQL authentication, the corresponding SQL Server login
should be restricted by permissions in the database.
     Consider the example of an ASP.NET application running on Microsoft Windows
Server 2003 that accesses a database on a different server in the same domain. By
default, the ASP.NET application runs in an application pool that runs under the
Network Service account. This account is a least privileged account.

8.4.3. To Access SQL Server with the Network Service Account

Create a SQL Server login for the Web server's Network Service account. The Network
Service account has network credentials that are presented at the database server as the
identity DOMAIN\WEBSERVERNAME$. For example, if your domain is called XYZ
and the Web server is called 123, you create a database login for XYZ\123$.
     Grant the new login access to the required database by creating a database user
and adding the user to a database role.
     Establish permissions to let this database role call the required stored procedures
or access the required tables in the database. Only grant access to stored procedures the
application needs to use, and only grant sufficient access to tables based on the
application's minimum requirements.
     For example, if the ASP.NET application only performs database lookups and does
not update any data, you only need to grant read access to the tables. This limits the
damage that an attacker can cause if the attacker succeeds in a SQL injection attack.

8.4.4. Avoid Disclosing Error Information
Use structured exception handling to catch errors and prevent them from propagating
back to the client. Log detailed error information locally, but return limited error details
to the client.
     If errors occur while the user is connecting to the database, be sure that you
provide only limited information about the nature of the error to the user. If you
disclose information related to data access and database errors, you could provide a
malicious user with useful information that he or she can use to compromise your
database security. Attackers use the information in detailed error messages to help
deconstruct a SQL query that they are trying to inject with malicious code. A detailed
error message may reveal valuable information such as the connection string, SQL
server name, or table and database naming conventions.


To top