Parameterized queries, also called prepared statements, process SQL input by incorporating placeholders for some of a query's parameters. When the query is executed, the web app binds the actual values to these parameters in a different statement. So, a quotation mark in a parameterized query would be interpreted literally, rather than be interpreted as if it were a part of the query structure. The input x' OR 'x'='x in the user name field of a login form would force the database to look for a user name that literally matched x' OR 'x'='x in its records.
Parameterized queries are the most effective means of preventing SQL injection attacks. Like other forms of input sanitization, how you implement parameterized queries will differ based on language. For example, PHP uses an abstraction layer called PHP Data Objects (PDO) for processing database content:
<?php
$prod_name = ""
$prod_desc = ""
// Code to connect to database...
// Prepare statement
$stmt = $db_conn->prepare("INSERT INTO products (prod_name, prod_desc) VALUES (:prod_name, :prod_desc)");
$stmt->bindParam(':prod_name', $prod_name);
$stmt->bindParam(':prod_desc', $prod_desc);
?>The INSERT INTO query is prepared, essentially creating a template for the database to parse. This parsed template is stored without being executed. The input values for $prod_name and $prod_desc are then bound to each parameter and transmitted after the query itself. When plugged into the template, the input values are executed literally, preventing the web app from succumbing to injected code.
Example for .NET Core 5 using C#:
private static void UpdateDemographics(Int32 customerID,
string demoXml, string connectionString)
{
// Update the demographics for a store, which is stored
// in an xml column.
string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
+ "WHERE CustomerID = @ID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;
// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue("@demographics", demoXml);
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}