SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input. Many web developers are unaware of how SQL queries can be tampered with, and assume that a SQL query is a trusted command. SQL queries are actually able to circumvent access controls, thereby bypassing standard authentication and authorization checks.
Since SQL statements are text only, it is easy to dynamically change SQL statements to provide the user with selected data:
URL
https://www.example.com/default.aspx?UserId=2
Query
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.
Injected SQL commands can alter SQL statements and compromise the security of a web application. The above where condition could easily be qualified by a SQL injection. For instance, consider if "UserId" was equal to: "2 OR 1=1". That would make the SQL query:
txtSQL = "SELECT * FROM Users WHERE UserId = 2 OR 1 = 1";
Since the statement above is true, it should further be appended to include destructive and malicious code. Most databases support batched SQL statements, separated by semicolon. Consider if "UserId" was equal to: "2 OR 1=1; DROP TABLE *". That would make the SQL query:
txtSQL = "SELECT * FROM Users WHERE UserId = 2 OR 1 = 1; DROP TABLE *";
Now your databas has no tables. So how do we prevent an action like this?
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.
The only proven way to protect a website 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);
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();