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