SQL Injection Attacks - Prevention

SQL Injection

What is SQL Injection attack ?

SQL Injection attack happens through passing the malicious data as input ( such as username , password containing ILLEGAL QUERIES OR STRINGS in the form submission in a page ) from client to server , which can lead to problems such as exposing the sensitive data to attackers, modifications of data in database , illegal execution of administration operations on database such as shutdown.

Examples of SQL Injection Attacks :

Whenever SQL Queries are constructed dynamically based on the client input , one can inject malicious SQL if Query construction and execution is not done properly as shown in below examples.

1) SQL Injection through login forms ( Illegal Login using SQL Injection ) :

Lets assume that the SQL Used for Login is the following :

String query = "SELECT * FROM users WHERE username = '" + request.getParameter("username") +  "' and password = '"  + request.getParameter("password") + "'";

If one provides :
username = anything
password = ' OR 'a' = 'a

Login Query becomes :

SELECT * FROM users WHERE username = 'anything' and password = '' OR 'a' = 'a' ;

Above query returns all users and finally it results in successful login by ATTACKER.

2) Get user details ( Deleting a table with SQL Injection ):

String query = "SELECT * FROM users WHERE username = '" + request.getParameter("username") + "'";

If one provides :

username = '; DROP TABLE resource;--

Note : -- is a comment is some databases like sql server

Query becomes :

SELECT * FROM users WHERE username = ''; DROP TABLE resource;--';

Here , two queries gets executed ( select query and drop table ) by the ATTACKER.

Prevention of SQL Injection Attacks :

Validations of data inputs , escaping the special characters in data such as single quotes can be done for preventing the attacks . But these are not perfect solutions since there are lot of illegal ways of doing SQL Injections exists now a days. Following are the correct solutions which can prevent almost all SQL Injection Attacks .


Solution 1 : Parameterized Queries

In Java, Parameterized queries can be prepared by using PreparedStatement as follows :

String username = request.geParameter("username");
String query = "SELECT  * FROM users WHERE username = ?";
PreparedStatement st = connection.prepareStatement( query );
st.setString( 1 , username );
ResultSet res = st.executeQuery();

Here, when the query gets executed , following two steps are performed :

i) First , Query with placeholders ( ? ) will get processed as SQL query by database

ii) Then, values ( username ) will applied in query and gets executed. Since there is no chance of treating the value ( username ) as SQL by database , SQL attacks or SQL Injections are not possible.


Solution 2 : Stored Procedures

Stored Procedures are another way of preventing SQL Injections. These work similar to PreparedStatements but SQL queries are defined and stored in database and called from the applications as follows :

String username = request.getParameter("username");
CallableStatement st = connection.prepareCall("{ call sp_getUserDetails(?) }");
st.setString( 1, username );
ResultSet res = st.executeQuery();


In addition to above, for avoiding SQL Injection attacks , its always preferred to do some validations ( such as length, format, special characters etc ) on data inputs either at client side or server side .

Do you have any other info or experience related SQL Injections ? Feel free to comment below !!!

References :






Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>