# SQL Injection

« Previous
Next Chapter »

An SQL Injection can destroy your database.

## SQL in Web Pages

In the previous chapters, you have learned to retrieve (and update) database data, using SQL.

When SQL is used to display data on a web page, it is common to let web users input their own search values.

Since SQL statements are text only, it is easy, with a little piece of computer code, to dynamically change SQL statements to provide the user with selected data:

## Server Code

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

The example above, creates a select statement by adding a variable (txtUserId) to a select string. The variable is fetched from the user input (Request) to the page.

The rest of this chapter describes the potential dangers of using user input in SQL statements.

## 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.

## SQL Injection Based on 1=1 is Always True

Look at the example above, one more time.

Let's say that the original purpose of the code was to create an SQL statement to select a user with a given user id.

If there is nothing to prevent a user from entering "wrong" input, the user can enter some "smart" input like this:

UserId:

## Server Result

SELECT * FROM Users WHERE UserId = 105 or 1=1

The SQL above is valid. It will return all rows from the table Users, since WHERE 1=1 is always true.

Does the example above seem dangerous? What if the Users table contains names and passwords?

The SQL statement above is much the same as this:

SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1

A smart hacker might get access to all the user names and passwords in a database by simply inserting 105 or 1=1 into the input box.

## SQL Injection Based on ""="" is Always True

Here is a common construction, used to verify user login to a web site:

User Name:

Password:

## Server Code

uName = getRequestString("UserName");
uPass = getRequestString("UserPass");
sql = "SELECT * FROM Users WHERE Name ='" + uName + "' AND Pass ='" + uPass + "'"

A smart hacker might get access to user names and passwords in a database by simply inserting " or ""=" into the user name or password text box.

The code at the server will create a valid SQL statement like this:

## Result

SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""

The result SQL is valid. It will return all rows from the table Users, since WHERE ""="" is always true.

## SQL Injection Based on Batched SQL Statements

Most databases support batched SQL statement, separated by semicolon.

## Example

SELECT * FROM Users; DROP TABLE Suppliers

The SQL above will return all rows in the Users table, and then delete the table called Suppliers.

If we had the following server code:

## Server Code

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

And the following input:

User id:

The code at the server would create a valid SQL statement like this:

## Result

SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers

## Parameters for Protection

Some web developers use a "blacklist" of words or characters to search for in SQL input, to prevent SQL injection attacks.

This is not a very good idea. Many of these words (like delete or drop) and characters (like semicolons and quotation marks), are used in common language, and should be allowed in many types of input.

(In fact it should be perfectly legal to input an SQL statement in a database field.)

The only proven way to protect a web site from SQL injection attacks, is to use SQL parameters.

SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.

## ASP.NET Razor Example

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);

Note that parameters are represented in the SQL statement by a @ marker.

The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.

## Another Example

txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);

| You have just learned to avoid SQL injection. One of the top website vulnerabilities.

## Examples

The following examples shows how to build parameterized queries in some common web languages.

ASP.NET SELECT

txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserID);
command.ExecuteReader();

ASP.NET INSERT INTO

txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtNam);
command.Parameters.AddWithValue("@1",txtAdd);
command.Parameters.AddWithValue("@2",txtCit);
command.ExecuteNonQuery();

PHP INSERT INTO

$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City)
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();
« Previous
Next Chapter »

### WEB HOSTING

UK Reseller Hosting

### WEB BUILDING

FREE Website BUILDER Free HTML5 Templates

### W3SCHOOLS EXAMS

HTML, CSS, JavaScript, PHP, jQuery, XML, and ASP Certifications

### SHARE THIS PAGE

### Color Picker

### HTML/CSS

HTML Tutorial HTML5 Tutorial CSS Tutorial CSS3 Tutorial Bootstrap Tutorial

### JavaScript

JavaScript Tutorial jQuery Tutorial jQuery Mobile Tutorial AngularJS Tutorial AJAX Tutorial JSON Tutorial

### Graphics

Canvas Tutorial SVG Tutorial Google Maps Tutorial

### Server Side

SQL Tutorial PHP Tutorial ASP Tutorial ASP.NET Tutorial VBScript Tutorial AppML Tutorial

### Web

Web Building Web Statistics Web Certification

### XML

XML Tutorial DTD Tutorial Schema Tutorial XML DOM Tutorial XPath Tutorial XSLT Tutorial XQuery Tutorial XSL-FO Tutorial RSS Tutorial WSDL Tutorial

### HTML/CSS

HTML Tag Reference HTML Event Reference HTML Color Reference CSS 1,2,3 Reference Bootstrap Reference

### JavaScript

JavaScript Reference HTML DOM Reference jQuery Reference jQuery Mobile Reference Google Maps Reference

### Server Side

PHP Reference SQL Reference ASP Reference VBScript Reference Razor Reference ASP.NET Reference

### XML

XML DOM Reference XSLT Reference XSL-FO Reference XPath Reference XQuery Reference Schema Reference RSS Reference SVG Reference

### HTML Charsets

HTML Character Sets HTML ASCII HTML ANSI HTML Windows-1252 HTML ISO-8859-1 HTML Symbols HTML UTF-8

### HTML/CSS

HTML Examples CSS Examples

### Browser Scripting

JavaScript Examples HTML DOM Examples jQuery Examples jQuery Mobile Examples AngularJS Examples AJAX Examples

### Server Scripting

PHP Examples ASP Examples VBScript Examples Razor Examples .NET Examples

### XML Languages

XML Examples DTD Examples XML DOM Examples XSL Examples XSLT Examples XPath Examples XQuery Examples Schema Examples RSS Examples SVG Examples