SQL Injection

What is SQL Injection?

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input.

Injected SQL commands can alter SQL statement and compromise the security of a web application.

A SQL injection attack can occur when a web application utilizes user-supplied data without proper validation or encoding as part of a command or query.

SQL injection errors occur when:

Ø  Data enters a program from an untrusted source.

Ø  The data used to dynamically construct a SQL query.

Ø  Weak input validation.

Key Concepts of a SQL Injection Attack

Ø  SQL injection is a software vulnerability that occurs when data entered by users is sent to the SQL interpreter as a part of a SQL query.

Ø  Attackers provide specially crafted input data to the SQL interpreter and trick the interpreter to execute unintended commands.

Ø  A SQL injection attack exploits security vulnerabilities at the database layer. By exploiting the SQL injection flaw, attackers can create, read, modify or delete sensitive data

 SQL Injection Example 

SELECT id FROM tblUser

WHERE Username = ‘username’ AND

Password = ‘password’

An attacker can also comment out the rest of the SQL statement

OR '1'='1' –-

' OR 1=1;--

' DROP TABLE tblUser –-

' DROP DATABASE DBName –-

Show all records from the table “tblUser” for a username and password supplied by a user

=1' OR '1' = '1

a’ OR 1=1 OR ‘b

' OR 1=1;--

‘ OR ‘’ = ‘’ –-

‘ OR ‘1’ = ‘1’ --

You can also try

‘ UNION SELECT 1,1 From Users --

‘ INSERT INTO tblUser (Username ,Password) –-

‘ DELETE tblUser --

Guess the username or password

Admin’ –-

User’ –

Admin’ #

Admin’ /*

What Can Be Done to Prevent SQL Injection Attacks?

1.  Constrain Input

Always validate user input by format, range, length. Constraining the input, you can protect your application.
In asp.net always put validation using server side validation control. Do not use client-side validation because can easily bypass client-side validation.
Prevent typing of characters such as semi-colon (;), dash (-) or percentage (%) that used in SQL Queries.

v  By javascript

<script type="text/JavaScript">

         Function Validate (txt) {

                 txt.value = txt.value.Replace(/[^a-zA-Z 0-9\n\r]+/g, '');

         }

</script>

<input type="text" id="txt" onkeyup="Validate (this)" />

v  Using ASP.Net FilteredTextBoxExtender

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

<asp:FilteredTextBoxExtender ID="FilteredTextBoxExtender6" runat="server" FilterType="Custom, Numbers, UppercaseLetters, LowercaseLetters" TargetControlID="TextBox1" ValidChars=".@" />

v  You can constrain its input by using a RegularExpressionValidator control as shown in the following

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

<asp:RegularExpressionValidator runat="server" ID="rexNumber" ControltoValidate=" TextBox1" ValidationExpression="^[0-9]{4}$" ErrorMessage="Please enter a 4 digit number!" />

2.  Parameterized Query

Parameterized Queries are those in which values are passed using SQL Parameters. Instead of string concatenation you need to add parameters to the Query and pass parameter value using the SqlCommand object.

Simple Select Query

String strQuery = "select * from tblUser";

SqlCommand cmd = new SqlCommand(strQuery);

Parameterized Query

String strQuery = "select * from tblUser where UserName = @username";

SqlCommand cmd = new SqlCommand(strQuery);

cmd.Parameters.AddWithValue("@username ", txtUserName.Text);

3. Stored procedures 

Another way of preventing SQL Injection is using Stored Procedure.

If you do use stored procedures, you need to pass the name of the stored procedure instead of the SQL statement, and you must set the CommandType to CommandType.StoredProcedure. It's omitted at the moment because the default is CommandType.Text.

CREATE PROCEDURE [dbo].[GetUserDetail]

      @UserName VARCHAR(40)

AS

BEGIN

       DECLARE @query NVARCHAR(2000)

       SET @query = 'SELECT * FROM tblUser WHERE UserName = @UserName'

       EXEC sp_executesql @SQL, N'@UserName VARCHAR(40)', @UserName = @UserName

END

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "GetUserDetail";

cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = txtUserName.Text.Trim();