This post is in continuation of my previous post on String Append Vs Parameter Binding. In this post we are going to see the advantages of Parameter Binding over String Append. Generally in our daily to daily coding practice we never consider these advantages and most of the time we try to take a path that is much easier. In taking these easier paths most of the times we compromised the software security , its performance and introduced new bugs , that we might have missed during our testing after development. One of those bugs I have explained in my previous post here.
Below are the advantages that you will get if you rely on Parameter Binding :
Performance : First advantage that we will get if we use parameter binding is having the overall improvement of the application performance. Lets see how it will increase the performance – Today almost all the RDBMS databases supports query execution plan cache , having execution plan in place SQL engine can use the same execution plan again and again , only with different set of values passed. By doing this its saves the time in building the execution plan again and again. So, here is the difference when you pass the values in SQL directly , then SQL engine considers it a new sql statement and generates the execution plan everytime, on the other hand when we use parameter binding , the whole sql statement remains as it as and at the run time we are providing the values to those bind variables.
Let’s look at the same thing in terms of some High Level Programming language like ,C , Java etc. If you are familiar with any of these languages then you know how the variables and constants are treated in these languages. So, here we are going to use the same reference for comparison, when you pass values directly into the SQL statements it is something like that you are defining constants in your program and there values being determined during the compilation itself that is a costlier operation in comparison to when you pass values as a variable ,theirs value have been resolved during the run time same as when you pass value as bind variables
SQL Injection : Whenever you are passing all the values(through String concatenation) in the SQL Query ,you are always exposing your application for the SQL Injection attacks. Let’s have a look on how we are exposing – I will use an example to demonstrate the same.
e.g : Let’s assume we have a simple application that displays all the employees having salary more than supplied value in input. We have a simple form that have one textfield (for accepting salary) and one button for submitting the form.
In our first case we are using String Append for the query execution, so our query will look like this :
String salary=request.getParameter(“salary”); //10000 String query = “Select * from Employee where salary > “+salary;
Till now everything looks fine , when we execute the above query it will give us the all the employees having salary more than 10000. But imagine if some hacker manipulated this value in such a way that it changed the essence of query itself. So, instead of passing “10000” in the textfield , he passed the “10000 OR 1=1” , then think what will happen , our query will look like this.
Select * from Employee where salary>10000 or 1=1;
Once this query is executed you will see all the employees exists in the table and with their corresponding salaries , ideally you should be able to see only filtered results not all.
This type of sql injection can be avoided if we use the parameter binding instead . So, our code will look like this.
String salary=request.getParameter(“salary”); //10000 String query = “Select * from Employee where salary > ?“; Ps.setInteger(0,salary);
Note : Above mentioned code is just only for demonstration purpose.
Now in the above code if hacker tries to enter some malicious values , it will through an exception as salary value is not a valid integer. Hence , hacker will not be able to see the unwanted data.
This is just a very small example of SQL Injection , ideally it is more complex in comparison to what I have presented here.
Hope , you have enjoyed this post, please drop your comments or suggestions.
Thanks for visiting
Latest posts by Saurabh Jain (see all)
- java.lang.IncompatibleClassChangeError: Found interface org.apache.hadoop.mapreduce.TaskInputOutputContext, but class was expected - August 8, 2014
- org.datanucleus.store.rdbms.exceptions.MappedDatastoreException: INSERT INTO “TABLE_PARAMS” – Hive with Kite Morphlines - July 17, 2014
- java.io.IOException: can not read class parquet.format.PageHeader: null – Hive - July 12, 2014