The Best Way to Prevent SQL Injection

 When dealing with data either through the URL or via a user submitted form, the best way to prevent SQL injection is to investigate prepared SQL statements OR parameterized queries in whatever language you are using.  Let's look at several different examples of a prepared SQL statement in a couple of different languages.

Let's begin with PHP.  To create a prepared SQL statement in PHP I will leverage the prepare and bind_param functions that are part of the mysqli library:

$query = $db->prepare('SELECT * FROM user WHERE email = ?');
$query->bind_param('s', $_POST['email']);
$query->execute();
$result = $query->get_result();

By placing a ? in the SQL query, I then use the bind_param function to retrieve the email address from a $_POST variable and tell SQL that I'm passing in a string with the s as the first parameter.  This can be done for integers, dates, etc…

Now let's look how to accomplish prepared SQL statements using C# .NET:

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("SELECT * FROM users WHERE email = @Email", conn))
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = Settings.Default.reportTimeout;
cmd.Parameters.Add("@Email", SqlDbType.VarChar, 255).Value = Email;
cmd.Connection.Open();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(ds);
}
}

And for fun, let's also do this in ColdFusion:

<cfquery name="GetUser" datasource="datasource">
SELECT * FROM users WHERE email =
<cfqueryparam value="#Email#" CFSqlType="CF_SQL_VARCHAR" maxlength="255">
</cfquery>

In all of the above scenarios, the database libraries will either automatically remove any characters that do not match the parameter type defined or generate a query error.  Either scenarios allows for a safe and secure approach to your SQL queries to prevent SQL injection.

Published on Sep 19, 2012

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | php | ASP.NET MVC and Web API Tutorial | c# | PHP