The article will mainly talk about some pitfalls or important command of general SQL, just mark them and for future reference. Most of the content comes from SQL tutorial .
1. 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 + ‘”‘
Result
SELECT * FROM Users WHERE Name =”John Doe” AND Pass =”myPass”
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:
User Name:
Password:
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.
SELECT STATEMENT IN ASP.NET:
txtUserId = getRequestString(“UserId”);
sql = “SELECT * FROM Customers WHERE CustomerId = @0”;
command = new SqlCommand(sql);
command.Parameters.AddWithValue(“@0”,txtUserID);
command.ExecuteReader();
INSERT INTO STATEMENT IN ASP.NET:
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();
INSERT INTO STATEMENT IN PHP:
$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();
2. SQL INNER JOIN Keyword
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
PS! INNER JOIN is the same as JOIN.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the “Customers” table:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
1
Alfreds Futterkiste
Maria Anders
Obere Str. 57
Berlin
12209
Germany
2
Ana Trujillo Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México D.F.
05021
Mexico
3
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico
And a selection from the “Orders” table:
OrderID
CustomerID
EmployeeID
OrderDate
ShipperID
10308
2
7
1996-09-18
3
10309
37
3
1996-09-19
1
10310
77
8
1996-09-20
2
SQL INNER JOIN Example
The following SQL statement will return all customers with orders:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the “Customers” table that do not have matches in “Orders”, these customers will NOT be listed.
Half Step 