New Post


Prevent SQL Injection via Prepared Statements or Parameterized Queries

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 prepared statements 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 Parameterized Queries.

Prevent SQL Innjection Using Parametrized queries
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 one parameter 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");
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:

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.

Prevent SQL Injection attacks by Hackingloops - Part 1

Prevent SQL Injection attacks by Hackingloops - Part 1




Prevent SQL Injection
Prevent SQL Injection


SQL Injection : Basic Introduction




Let us learn two God Principles in detail:

Dynamic SQL Queries


Consider an example: Consider the below dynamic SQL

Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery(query);

SELECT * FROM items WHERE owner = AND itemName = ;


 WHERE owner = 'Lokesh'
 AND itemName = 'a'='a';


SELECT * FROM items;


stmt.setString(1, userName);
stmt.setString(2, itemName);
ResultSet rs = stmt.executeQuery();


Incorrectly Filtered Escape Sequences or Types



MySQL supports two types of Escaping modes:
2. MySQL mode



 NUL (0x00) --> \0  
 BS   (0x08) --> \b
 TAB (0x09) --> \t
 LF    (0x0a) --> \n
 CR   (0x0d) --> \r
 SUB (0x1a) --> \Z
 "      (0x22) --> \"
 %    (0x25) --> \%
 '      (0x27) --> \'
 \      (0x5c) --> \\
 _     (0x5f) --> \_ 
 where 'c' is the original non-alphanumeric character.


Consider an below example :

Statement stmt = connection.createStatement();




which is actually equivalent to 









11 Firefox Addons a Hacker Must Have and use

11 Firefox Addons a Hacker Must Have and use

Firefox is one the most secured web browser in the world. Have you ever dreamed of that we can use Firefox to Hack like a pro? Firefox like other browsers has a feature called add-on. Add-ons adds an additional functionality to your Firefox browser. There are thousands of Firefox add-ons available for Mozilla but Hackingloops brings you the best and most effective Hacking add-ons ever on Firefox. In short, we are listing a most popular and interesting Firefox add-ons that are useful for Hackers. This list of 11 add-ons vary from information gathering tools to attacking tools. All these add-ons are available for free and you can download from the Mozilla add-on website. So friends lets see what Hackingloops has bring this time for you. I will list them in way from top( I like most) to bottom pattern but note that all of them are extremely good tools.

Firefox Add-ons for Hackers
Firefox Add-ons for Hackers

11 Firefox Add-ons a Hacker Must Have and use

1. Tamper Data
Add Tamper data to Firefox:

2. Firebug
Add firebug to your browser :
3. Hackbar
Add Hackbar to Firefox:

4. Cookies Manager +
Add Cookies Manager to Firefox:

5. NoScript
Add NoScript to Firefox:

6. Grease Monkey
Add Grease Monkey to Firefox :

7. User Agent Switcher

Add user agent Switcher to Firefox:

8. CryptoFox
Add CryptoFox to Firefox:

9. SQL Inject Me
Add SQL Inject Me to Firefox: 

10.  XSS ME
Add XSS ME to Firefox:

11.  Passive Recon

Add Passive Recon to Firefox:

That's all for today guys, i hope you all are enjoying your journey towards becoming a Professional Hacker. Have fun! Keep Learning.
How to become a Professional Hacker | Hackingloops

How to become a Professional Hacker | Hackingloops


How to become a Professional Hacker
How to become a Professional Hacker

How i got an inspiration to become an Hacker? 




How to become a Professional Hacker?

10 fundamental Steps to become a Professional Hacker:












How to Open links in India and other countries


Unblock links in India
How to Unblock links in India

How ISP's Block Website in Countries?






1. Say you want to visit the website link


3. That's all.

Method 2: Adding a sub-domain to the website.

1. Say you want to visit the website link


3. That's all.


Designed by Hackingloops.