The Best Way to Prevent SQL Injection

If you use a framework of some sort, you probably haven’t thought about SQL injection for some time – in fact it almost seems dated to even discuss it.  However, security should never be overlooked and it’s important to not trust third party applications and people by default!  So what is the best way to prevent SQL injection?

Have you noticed how I haven’t specified a specific language?  This is done purposely, because at the end of the day – all languages – should be able to follow this paradigm…

 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:

[code]
$query = $db->prepare(‘SELECT * FROM user WHERE email = ?’);
$query->bind_param(‘s’, $_POST[’email’]);

$query->execute();
$result = $query->get_result();
[/code]

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:

[code]
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);
}
}
[/code]

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

[code]
<cfquery name=”GetUser” datasource=”datasource”>
SELECT * FROM users WHERE email =
<cfqueryparam value=”#Email#” CFSqlType=”CF_SQL_VARCHAR” maxlength=”255″>
</cfquery>
[/code]

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.