Prevent SQL Injection via Prepared Statements or Parameterized Queries

Hey Friends ! In our last tutorial on How to prevent SQL Injection, we have learned how Dynamic SQL queries and Escape Sequence & type handling makes our website vulnerable to SQL Injection attacks. Well if you ask me the reason, then i will say its only because of lack of awareness of secure coding standards. If web developers know how to code securely and efficiently then we all can avoid SQL Injection. So friends, today we will learn our very first article on how to write SQL codes securely using prepared statements. Queries written using preparedstatements are also referred as Parameterized Queries. But before everything we must understand what is Parameterized Query and then we will learn how to write Parameterized Queries in different web application Languages in secure way. I will explain in the Languages that i know(i.e. Core Java, Java Hibernate, .NET, ASP.NET, PHP and PERL) rest you can learn from other references. So friends lets start our tutorial on How to Prevent SQL Injection via Prepared Statements or ParameterizedQueries.

Prevent SQL Innjection Using Parametrized queries

What is Parameterized Query in respect to SQL?

Parameterized Query is basically an type of SQL Code which requires at least oneparameter for its execution. A Standard placeholder i.e. "?" (without quotes) is normally substituted for the parameter in the SQL Query and then the parameter is passed to query in the separate statement. Still not clear?

Consider an example : I wish to write an Dynamic SQL Query where i have to select something from some table based on some condition. Say i have a table namely "USERS_DATA" and i have to extract the records when custName = spaces. Now how an normal user will write the Dynamic SQL Query:

String query = "SELECT * FROM users_data WHERE user_name = "
   + request.getParameter("custName");

 try {
     Statement statement = connection.createStatement( );
     ResultSet results = statement.executeQuery( query );
 }

But the above Dynamic SQL is insecured and its vulnerable to SQL Injection because custName variable is not properly handled.

Now if i wish to write the Parameterized Query for the above Dynamic SQL Query, then i have to first request the parameter custName and it should be validated for input validation attacks. Then i will pass "?" in the user_name field value and in the next statement i will pass the value of custName field into query in new statement. How it will look like :

String customerName = request.getParameter("custName");  // perform input validation
String query = "SELECT * FROM users_data WHERE user_name = ? ";

 PreparedStatement pstmt = connection.prepareStatement( query );
 pstmt.setString( 1, customerName);
 ResultSet results = pstmt.executeQuery( );

Now you can see how a parametrized query actually looks like. ? is standard placeholder and custName field is passed after input validation checks. 

I hope this gives you basic understanding of What is parametrized Query and how we should write. Our main goal is to understand how it will help us to avoid SQL Injection attacks. So lets see the actual concept running behind it.

Prepared Statements (or Parameterized Queries)

All developers should understand the concept of using Prepared statements or parametrized queries before writing actual Database Queries. Because this will not secure your website but also it will increase the readability of the SQL code and will save you efforts in fixing and understanding things.
Using Prepared statements is just an another way to write Dynamic queries but faster, safer and easier way. Parametrized Queries force web developers to first write the complete SQL code , and then pass each parameter as per requirement. This helps in distinguishing between code and data regardless of what user input has been supplied to SQL code. This is the step where it handles the user input via validation and hence secures the SQL Injection attacks.

So by prepared statements, web developers prevent hacker from changing the actual processing or functionality of SQL query. But How? In the example of Parametrized Query i have shown above if Hacker passes some Blind SQL String say 'a'='a' then what Query will do is, it will check for the customer name who has name 'a'='a' rather that logically interpreting it. So it prevented the SQL Injection. Isn't it? Off-course it does. 

Below is the List of Standard Functions available in Languages that supports Parameterized Queries:

• Java : Use PreparedStatement() with bind variables 
• .NET /C# : Use SqlCommand() or OleDbCommand() with bind variables 
• PHP : Use PDO (PHP Data Objects) using function bindParam()
• Java Hibernate : use createQuery() with bind variables ( These are also called as named parameters)
• SQLite : use sqlite3_prepare() to create a statement Object.

 Usually use of prepared statements won't hinder the performance of the SQL Query but if you think its hampering the performance, then the best possible solution is to escape all user supplied input using an escaping routine specific to your database, rather than using Prepared statements. For such situation we have another secure coding standard called use of Stored procedures that we will discuss in upcoming article of the series.

Now its time to see how to write parametrized query in different Languages of the above unsafe Dynamic SQL.

1. Core Java or Standard Java : We have already seen that above but still for easy navigation sake i will write it again here. In Standard Java we have function prepareStatement() to write parametrized queries.

String customername = request.getParameter("custName");
String query = "SELECT * FROM users_data WHERE user_name = ? ";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, customername);
ResultSet results = pstmt.executeQuery( );

  

 2. .NET / C# : As explained above we use SqlCommand() or OleDbCommand() with bind variables to write parametrized queries in .NET. Let's see its usage:

String query = "SELECT * FROM users_data WHERE user_name = ?";
try {
   OleDbCommand command = new OleDbCommand(query, connection);
   command.Parameters.Add(new OleDbParameter("custName", CustName Name.Text));
   OleDbDataReader reader = command.ExecuteReader();
   // …
} catch (OleDbException se) {
   // error handling
} 

3. PHP : As i have stated above PHP uses PDO (PHP Data objects) using bindParm() to write the parametrized queries. Lets see how it looks practically :

$stmt = $pdo->prepare('SELECT * FROM users_data WHERE custName = :custName');
$stmt->execute(array(':custName' => $custName));
foreach ($stmt as $row) {
    // do something with $row
}

4. Java Hibernate : In Java Hibernate, we use createQuery() function to write the parametrized queries. Lets see how it looks:

Query safeHQLQuery = session.createQuery("from users_data where custName=:custName");
safeHQLQuery.setParameter("custName", userSuppliedParameter);

5. ASP.NET : In ASP.NET we have a function called sqlParameter() to write parametrized SQL Queries. Lets have a look:

string sql = "SELECT * FROM users_data WHERE custName = @custName";
SqlCommand command = new SqlCommand(sql);
command.Parameters.Add(new SqlParameter("@custName", System.Data.SqlDbType.char));
command.Parameters["@custName"].Value = ;

Well that's all about Prepared Statements or Parameterized queries. I hope you all understands the concept that how we an prevent SQL Injection using Prepared statement or Parameterized Queries.

In our Next article on How to Prevent SQL Injection, we will learn how to use Stored Procedures to prevent SQL Injection.

Have Fun! Keep Learning. If you have any queries ask me in form of comments.