The Best Way to Prevent SQL Injection 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:

$query = $db->prepare('SELECT * FROM user WHERE email = ?');
$query->bind_param('s', $_POST['email']);
$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;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))

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">

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

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.


Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.