SQL Injection – How to Test Web Applications against SQL Injection Attacks

Security testing of web applications against SQL Injection, explained with simple examples – By Inder P Singh.

Many applications use some type of a database. An application under test might have a user interface that accepts user input that is used to perform the following tasks:

1.    Show the relevant stored data to the user e.g. the application checks the credentials of the user using the log in information entered by the user and exposes only the relevant functionality and data to the user

2.    Save the data entered by the user to the database e.g. once the user fills up a form and submits it, the application proceeds to save the data to the database; this data is then made available to the user in the same session as well as in subsequent sessions

Some of the user inputs might be used in framing SQL statements that are then executed by the application on the database. It is possible for an application NOT to handle the inputs given by the user properly. If this is the case, a malicious user could provide unexpected inputs to the application that are then used to frame and execute SQL statements on the database. This is called SQL injection. The consequences of such an action could be alarming.

The following things might result from SQL injection:

1. The user could log in to the application as another user, even as an administrator.

2. The user could view private information belonging to other users e.g. details of other users’ profiles, their transaction details etc.

3. The user could change application configuration information and the data of the other users.

4. The user could modify the structure of the database; even delete tables in the application database.

5. The user could take control of the database server and execute commands on it at will.

Since the consequences of allowing the SQL injection technique could be severe, it follows that SQL injection should be tested during the security testing of an application. Now with an overview of the SQL injection technique, let us understand a few practical examples of SQL injection.

Important: The SQL injection problem should be tested only in the test environment.

If the application has a log in page, it is possible that the application uses a dynamic SQL such as statement below. This statement is expected to return at least a single row with the user details from the Users table as the result set when there is a row with the user name and password entered in the SQL statement.

SELECT * FROM Users WHERE User_Name = ‘” & strUserName & “‘ AND Password = ‘” & strPassword & “’;”

If the tester would enter John as the strUserName (in the textbox for user name) and Smith as strPassword (in the textbox for password), the above SQL statement would become:

SELECT * FROM Users WHERE User_Name = ‘John’ AND Password = ‘Smith’;

If the tester would enter John’– as strUserName and no strPassword, the SQL statement would become:

SELECT * FROM Users WHERE User_Name = ‘John’– AND Password = ‘Smith’;

Note that the part of the SQL statement after John is turned into a comment. If there were any user with the user name of John in the Users table, the application could allow the tester to log in as the user John. The tester could now view the private information of the user John.

What if the tester does not know the name of any existing user of the application? In such a case, the tester could try common user names like admin, administrator and sysadmin. If none of these users exist in the database, the tester could enter John’ or ‘x’=’x as strUserName and Smith’ or ‘x’=’x  as strPassword. This would cause the SQL statement to become like the one below.

SELECT * FROM Users WHERE User_Name = ‘John’ or ‘x’=’x’ AND Password = ‘Smith’ or ‘x’=’x’;

------------

Since ‘x’=’x’ condition is always true, the result set would consist of all the rows in the Users table. The application could allow the tester to log in as the first user in the Users table.

Important: The tester should request the database administrator or the developer to copy the table in question before attempting the following SQL injection.

If the tester would enter John’; DROP table users_details;’—as strUserName and anything as strPassword, the SQL statement would become like the one below.

SELECT * FROM Users WHERE User_Name = ‘John’; DROP table users_details;’ –‘ AND Password = ‘Smith';

This statement could cause the table “users_details” to be permanently deleted from the database.

Though the above examples deal with using the SQL injection technique only the log in page, the tester should test this technique on all the pages of the application that accept user input in textual format e.g. search pages, feedback pages etc.

SQL injection might be possible in applications that use SSL. Even a firewall might not be able to protect the application against the SQL injection technique.

I have tried to explain the SQL injection technique in a simple form. I would like to re-iterate that SQL injection should be tested only in a test environment and not in the development environment, production environment or any other environment. Instead of manually testing whether the application is vulnerable to SQL injection or not, one could use a web vulnerability scanner that checks for SQL injection.

Related: Couple of months back Inder wrote an interesting article on “Security testing of web application”  Have a look at it for more details on different web vulnerabilities.



The Best Software Testing Training You'll Ever Get!

software testing QA training

88 comments ↓

#1 venkat Shree on 01.17.09 at 6:10 pm

SQL injection is ignored testing part in most of the companies. But as developers don’t pay attention on database security. its testers responsibility to secure application against such attacks. Take this responsibility.

Thanks for this article.

#2 Rajeshkumar on 01.18.09 at 4:53 am

This article is very much useful for the users who were interested to start sql injection. Thanks for this great work and keep going ..

#3 felix on 01.18.09 at 8:59 am

Hope have subsequent article ,more details about SQL injection.

#4 Arman on 01.18.09 at 11:00 am

Nice article, Hope to get more detail..

#5 Prashant on 01.19.09 at 5:04 am

Nice Article By Inder P Singh. I was aware of the Sql injection methodology but I was not sure about the implementation.. Thess Example are great releif for the Web and desktop application tester..

Many Many thaks to Inder. Hope to hear from you soon on “URL Re-Writing”. This is another security threat for web based application.

#6 yogni on 01.19.09 at 5:53 am

Hello Vijay,

But under testing, for e.g for login page

If the user is not allowed to enter any character other than alphabets and numbers in username and password fields.

Would it be enough to stop the SQL injection attack by putting validations on special chars, blank spaces and null values.

#7 Savitha on 01.19.09 at 7:18 pm

Very good article…SQL Injection explained in a simplified manner

#8 Inder P Singh on 01.20.09 at 10:30 am

@Yogni,

There are two reasons why the approach of disallowing any characters other than alphabets and numbers might not work:

1. Some web applications allow users to create usernames containing other characters e.g. a user is allowed to use an email address as a username, a user might use a user name with an apostrophe like O Brien.

Further, passwords with special characters are encouraged since the number of permutations of such passwords increases. This makes it more difficult for an attacker to guess a password.

2. Even if characters other than alphabets and numbers are not allowed on client-side, the attacker might intercept the HTTP traffic between the browser and the server with the help of tools like TamperIE, Fiddler etc and insert the special characters that were prevented in the client-side validation.

Hope this helps!
Inder P Singh

#9 Sachin Winkle on 01.20.09 at 1:38 pm

This article is very much useful for the users who were interested to start sql injection. Thanks for this great work.

#10 rajesh on 01.21.09 at 9:07 am

Is there any tool available to test sql injection

#11 Inder P Singh on 01.21.09 at 11:24 am

@Rajesh,

That is a good question. Not one but many tools and web vulnerability scanners are available to test SQL injection. However, when using a tool or a scanner, it is best to complement the test with manual testing because the tool or scanner might have limited features available. You can view few relevant tools at http://www.databasesecurity.com/sqlinjection-tools.htm

Thanks,
Inder P Singh

#12 Yogini on 01.21.09 at 6:08 pm

Hello Mr.Inder P Singh

So according to you what should be the test cases for testing these two text fields.

You told about allowing the special character under for password , that’s very true since all the bank forcefully suggest to keep the A/c password with some complex combination of chars, special chars & numbers.

Because here the security issue is on top priority, so under these circumstances what should the various test cases and test plans.

Regards,

yogini
(software test engineer)

#13 Manohar on 01.22.09 at 9:53 am

Hi,
“Software testing help” is highly helpful for me. I have a doubt, what will be the first deliverable from a tester side after getting the SRS of a project? whether integration test case or System test case or understanding document. As of now we are preparing a Understanding document then System test case for entire requirement. Please clear my doubt.

Thanks & Regards
Manohar

#14 Poncho on 01.22.09 at 6:19 pm

Nice article!
But I’m not agree with the first comment:

“its testers responsibility to secure application against such attacks. Take this responsibility.”

I think the correct way to say is:

“Its testers responsability to run SQL Injection testCases; the whole team must take the responsibility to secure application againts such attacks”

Maybe is only a word puzzle, but as testers we need to emphasize that the Quality is matter of all the team, not only the testers

Thanks!
Poncho

#15 moesha on 01.23.09 at 10:01 pm

Hello, i have a question. Does anybody know how to do security testing on a web base application using QTP?. If so can you pls e-mail me or contact me. I really do need to know about the security testing and how i go about it. e-mail: kajohnson00@gmail.com

Thanks

#16 Inder P Singh on 01.24.09 at 1:37 pm

@Rajesh,

There are many tools available to test SQL Injection. Examples of such tools include web vulnerability scanners.
Tips:
1. It might be better to choose a new tool (that checks for exploits discovered recently.)
2. You might want to use multiple tools that complement each other.
3. Instead of limiting yourself to the capabilities of the available tools, you might want to supplement testing with manual testing for SQL injection.

@Yogini,

Testing for SQL injection would depend on the following:
1. The flavor of SQL used by the application
2. The kind of query the application uses on the log in page
3. Results of the previous test

Here are a few test ideas that are based on the examples given in the article:
1. Check if entering an apostrophe character causes a SQL error in the application.
2. Guess a common user name and enter it in the field of user name along with a string terminator and a comment character e.g. —
3. Enter any user name along with a condition that is always true.

I will explain more test ideas in a future article.

Thank you both,
Inder P Singh

#17 Inder P Singh on 01.24.09 at 1:40 pm

@Moesha,

You are right. If any problem related to SQL injection has been found in the application, it would make sense to have automated tests to check the same in other areas of the application as well as in future releases of the application. However, keep in mind that execution of some test cases would depend on the success of the prior test case(s).

Thank you,
Inder P Singh

#18 Raghavan on 01.25.09 at 5:48 am

good article – there are many different variants of a sql injection attack – we at http://www.TestersDesk.com have a FREE SQL Injection Generator that takes form details and auto-creates a HTML that you can download and use to try different SQL Injection tests. Many users use this for testing their login page etc.

#19 Ravikumar on 01.28.09 at 6:10 am

Nice article, keep posting, many testers are waiting for some more interested information related to this topic

#20 Pallavi on 01.28.09 at 4:22 pm

Good Article

#21 seshu on 02.03.09 at 10:47 am

very good article

#22 Santosh.R on 02.05.09 at 10:55 am

please anyone answer to below mentioned java script, which is a web site hacking script.

How to handle this java script in DB.

Thanks in advance……….

#23 Santosh.R on 02.05.09 at 10:57 am

sorry even that script itself not displaying

#24 balu on 02.06.09 at 6:33 am

Thanks vijay for giving such a valuable inforamation on SQL Injections .

#25 Rajesh on 02.09.09 at 11:36 am

very nice article i am working as SQA i am interested in learning testing skills and i recently heard about SQL injection and testing that is explained very clearly.
thank you .

#26 Swagata Mukherjee on 02.09.09 at 1:00 pm

very nice article i am working as SQA i am interested in learning testing skills and i recently heard about SQL injection and testing that is explained very clearly.
thank you .“Software testing help” is highly helpful for me.
If there is a proper code then it will be better.

#27 Swagata Mukherjee on 02.09.09 at 1:03 pm

Magic String:–The magic string is a simple string of SQL used primarily at login pages. The magic string is
‘OR”=’
When used at a login page, you will be logged in as the user on top of the SQL table.

#28 praveen kumar on 02.16.09 at 9:55 am

Really, this article is very good to understand the concept of SQL Injection. After reading the example of SQL injection, I got very clear what is SQL Injection….
Very nice article….Hope U would write more article with Examples..

#29 jayakumar on 04.25.09 at 12:07 pm

thanks for u explanation,but i want complet know about testing procedure and how i learn.real time demos.

#30 Loron on 05.13.09 at 12:23 pm

this is a fairly good worthrough. I agree with jayakumar. a tut

#31 Ravishankar on 05.18.09 at 11:28 am

i Dont think SQL injection is neccessary as part of testing for all the web Applications.but it should be made part of test plan depending type of data it is dealing with say crucial personnel information which is high importance to the user say a gov website.In all usch cases it should be made part of testplan if needed

#32 Inder P Singh on 05.18.09 at 4:20 pm

@Ravishankar,

Security testing deals with not only the senstivity of information (as the crucial personnel information mentioned by you), it also deals with the availability of information. Therefore, if you have a web application that does not have any sensitive information but allows an attacker to gain control of the application/ database using the SQL injection technique, the attacker could cause a denial of service to the users. This means that all web applications could be benefited from security testing using the SQL injection technique.

Thanks for your comment!
Inder P Singh

#33 Vlad on 05.21.09 at 1:14 pm

@Inder: If I understood correctly, if the application is set to trow an error page (something like “An error has ocurred. Please contact your admin”) it greatly reduces the risk of sql injection attacks?

Thanks,
Vlad

#34 Inder P Singh on 05.22.09 at 1:40 pm

@Vlad,

An error page NOT containing the SQL error is better than the one that does because it does not help the attacker with further information. However, such a design may not reduce the risk of SQL injection attacks; it may simply increase the time required for the attack. This post is about testing a web application with the SQL injection technique but is not about ways to secure the application against the SQL injection technique.

Even though you have asked a simple question, I somehow get the feeling that you are quite knowledgeable about SQL injection. If so, would you like to share some of your knowledge on this topic?

Many thanks for your comment!
Inder P Singh

#35 Shakil Ahmed on 05.28.09 at 9:25 am

Excellent article to understand about SQL injection

#36 mallik on 08.07.09 at 1:31 pm

GOod and article with example about SQL injection.
Please post more about it

#37 Pratap on 08.17.09 at 7:11 am

Good article keep us informing more about SQL injection.
Thanks

#38 Pavan on 08.25.09 at 10:27 am

Can i know how can i use SQL injections when hibernate querying is used. As in HQL the query is dynamically generated and these specials characters are not allowed as there are validations to it.

#39 Pavan on 08.25.09 at 10:36 am

@inder
In one of ur previous posts you were talking abt attacking HTTP request before it reach to server, If i am not wrong this is CSS (Cross site Scripting) I used a tool Web Scrab to test the same where in i can stop the user entered parameters and i can pass my own parameters to the server. But during this process i faced a problem like we used HQL in the project where if i enter any invalid charecters then even though there is no validation but hibernate is throwing an Invalid Query exception. Is there any tools or mechanism where in i can break the HQL?

#40 salim on 08.27.09 at 10:09 am

Please explain 1. Null poison attack
2. XSS Vulnerabilities

#41 free download hollywood movies on 08.31.09 at 9:55 am

this is the nice tutorial for finding the sql injection, can you provide some documentation like test cases for better understanding, please.

#42 Rony Barua on 09.11.09 at 9:55 am

I need more details about SQL Injection……Some one can help me

#43 Sana on 09.25.09 at 9:53 am

Which free tool is best for SQL injection testing for a web application ?

#44 deepak bansal on 11.01.09 at 8:07 am

hi all,

I am creating a web app scanner for my major project for 4th year B.E in CSE.I need help in finding the right tools and technologies for its implementation.Some one can help me….

#45 Inder P Singh on 11.02.09 at 5:59 am

Deepak,

You should consider any one or more of Perl, Python or PHP programming languages to build your scanner. It would be well to be on the lookout for existing components that are available for you to use.

Thanks,
Inder P Singh

#46 geek blogger on 12.16.09 at 8:57 am

This is very nice and informative article. post some more things on sql injection.

#47 Dnyaneshwar Raut on 12.16.09 at 9:31 am

nice!!

#48 Raja on 12.19.09 at 7:04 am

hi to all,
am testing a web application with database for security testing, its username and password is username = admin
password = admin..can you tell me sql injection for this application?

#49 Inder P Singh on 12.21.09 at 6:51 am

@Raja,

Thank you for your question!

You should try out various combinations of the username and the password as partial SQL expressions to see if your application is susceptible to SQL injection. Some examples are:
1. Any name followed by an apostrophe
2. admin followed by an apostrophe
3. Username as John’ or ‘x’=’x and password as Smith’ or ‘x’=’x

Happy testing!
Inder P Singh

#50 poojas on 01.28.10 at 8:41 am

hi,
i have an application which is having a login page.How can we determine that the login credentials are not hard coded and it is really checking with the database entries to allow the login.
Please reply

#51 venkat on 01.28.10 at 9:30 am

hi Pooja,

can u explain in detail.

thanks
venkat
eesan652@hotmail.com

#52 Poojas on 01.28.10 at 10:24 am

hi venkat,

Suppose I have an e commerce web site.
So if you want to buy something you have to login with your credentials and if the the data you are giving is existing the DB,then only you will access to the next page.
Consider the name pooja is not in DB.So if i am giving the name as pooja,login should fail.
In this case let us take the case that the developer is coded like if username=”pooja” then allow(I dont knw programming,i m just giving the algorthm).Here if when you are giving the name as pooja,eventhough the name is not existing in the DB,it will allow you to go to the next page,since it is hard coded.
Here my question is as a tester how can we determine that the validation is really happening with the database and the name is not hard coded.
Thanks
pooja

#53 Inder P Singh on 02.04.10 at 8:39 am

Hi @Pooja,

Code review (either manual or automated) is a good way to find the presence of hard-coded values (among other problems). Also, if you are familiar with the database design and can get access to the database, you may be able to find out the user information there.

If you do not have any access to the application code or the database (in other words, you only have the website open in your browser), finding about hard coded user names may still be possible by noting the average times it takes to login with different user names. For example, if the username, Pooja, is hardcoded in the application it ought to log in faster than with another username, say Pooja1, which is present in the database. However, keep in mind that the success of this technique would depend on the assumption that most of the time in logging in is spent in validating the username and password. But, I think that it is worth a try.

Inder P Singh

#54 Ramu on 02.05.10 at 11:49 am

Suppose I have an e commerce web site.
can u explain in detail.
i have an application which is having a login page.How can we determine that the login credentials are not hard coded and it is really checking with the database entries to allow the login.
Please reply

#55 vineeth on 04.07.10 at 11:47 am

Nice article, thanks Inder

#56 jami on 09.20.10 at 5:24 pm

nice topic SQL Injection . i am a test trainee , i don’t any thing related to testing , how should i approach for learning testing , its so vast so many new things its interesting but also mixing . plz help me

#57 selva ganapathi on 10.29.10 at 1:15 pm

The coverage is good. But real time example for this attack would really awesome..

#58 raja on 11.26.10 at 11:45 am

Can u explain me the sample , where to add the sql injection , I mar\en how to test the application

#59 navnath on 12.31.10 at 11:11 am

i am fresher for testing fields… please explain briefly.

#60 sumathi on 03.25.11 at 9:05 am

Dear all,

Am working as a tester in a small company.. Am just testing for past three months only about interface, nevigation, re-testing and functionality flow of the web-application. I like to know more about testing techniques used in web-application..Because am the only tester available in the company . so i want to know few more techniques to use in real time…Kindly provide me information

#61 peeyush on 05.20.11 at 7:39 am

Plz can u elaborate tis article..actually i m new to security n facing alot of problem,wer to start the testing.

can anyone help regarding tis.

#62 suman on 06.13.11 at 5:34 am

This tutorial is very easy to understand…..and gives all the basic needs to an entry level testers

#63 darshani on 06.14.11 at 1:37 pm

superbb article….could you please elaborate more on its prevention techniques

#64 Gatz on 06.15.11 at 7:24 am

This is indeed a very good article… Thanks!

#65 Amarjeet on 07.07.11 at 4:05 am

Good One…. Gives more idea about how we can break the applications….

#66 Sudarshini on 08.11.11 at 8:07 pm

Lovely article. Before going through this post I really had no idea about SQL injection and how much it will be helpful in testing the applications that I will handle. Thanks to the author for writing it in such easy and picturesque form.

#67 How to Test Application Security – Web and Desktop Application Security Testing Techniques — Software Testing Help on 09.05.11 at 8:14 pm

[...] SQL Injection and XSS (cross site [...]

#68 suchi on 11.17.11 at 5:16 am

It is very much useful for testers

#69 Neha on 11.30.11 at 12:49 pm

Hi,
I am new to SQL Injection and very interested to learn it. Its really a good article for the beginner.
To start with, how we will come to know if application is vulnerable to SQL injection attacks or not.

#70 Anirudh Jain on 12.14.11 at 6:23 am

Hi

Recently i had start learning the concepts for SQL Injection. The article provided above is excellent but can you please provide some more practicle so that i can be very clear with the same

#71 manasa on 02.10.12 at 10:24 am

very very use full order,but i am having some doubt…if any attempt Pass..then how the results are going to display…i mean results shall be display in front end?
Any body pls clarify my doubt…Thanks in advance

#72 manasa on 02.10.12 at 10:25 am

very very useful article

#73 SQL Injection – How to Test Web Applications against SQL Injection Attacks — Software Testing Help « Catatan Saya on 02.28.12 at 3:01 am

[...] SQL Injection – How to Test Web Applications against SQL Injection Attacks — Software Testing He…. Like this:LikeBe the first to like this post. Tagged with: sql injection [...]

#74 Pradeep R H on 03.28.12 at 8:00 am

Its good reference for beginers….need some other examples also

#75 Ankit Singhal on 04.17.12 at 8:54 am

Can anyone please tell me the Sql Injection for below scenario:-

1. Enter URL and passing stored procedure or web service input parameters.
2. Then it connects to the database and do authentication, authorization and validation
3. That URL based on input parameters decides which takes to execute either SP or Web service.
4. Then collects data and pass to the other system.

Please help to test SQL injection.

Your suggestion will be appreciated.

#76 Ajay Meda on 05.09.12 at 7:12 am

Hi Inder,
Will the use of stored procedures avoid SQL Injection techniques?

Ajay

#77 Rakshitha on 06.25.12 at 3:48 am

Superb Article

#78 swathi on 06.29.12 at 5:35 am

really very very useful article…..

#79 sakthi on 08.29.12 at 1:41 pm

It is a nice one . i want more about xss

#80 kranthi on 09.07.12 at 11:42 am

Hi,
I’ve a doubt. Please clarify.
I’m testing a web application whose links connect to the developer database.

When entering values with single quote into the input fields, a server error is returned, which gives some lines of code and stack trace.

Is this correct? How should I proceed next?

Please clarify.

Thanks in advance.

#81 dfd on 11.03.12 at 8:39 pm

select * from dual

#82 Sachin on 01.19.13 at 1:03 pm

Nice Article but need some practical examples.

#83 jav/J2EE Developer on 01.21.13 at 5:25 pm

filtering blank characters and using prepared Statement would be a sufficient tools against SQL Injection
For Extra Security , we may filter all SQL keywords such as : or ,delete ,and , select ,from , where etc
PS: These security measures must be implemented at the server side , not the client side

#84 Fussball Trikots on 01.29.13 at 4:22 pm

Thanks for sharing your thoughts. I truly appreciate your efforts and I will be
waiting for your further post thanks once again.

#85 Bharadwaj on 06.06.13 at 12:17 pm

Can you please elaborate more on security testing and give me the tips on how to test the application.

#86 abhi on 06.24.13 at 10:08 am

Hi
I am not getting any proper Virtual server where i can check cross browser compatibility with MAC, since i dont have mac.

Kindly give me suggestions
Thanks
Abhi

#87 Naveen on 12.13.13 at 1:03 pm

Hi everyone,
my name is Naveen, i have 2 years exp in manual testing in windows application, recentle, i got an interview for web based testing, so they are asking me for paper presentaion on web based testing, so i request everyone, please send me the required documents to prepare for interview, my mail I D is naveen_mag@yahoo.com.

Thanks and regards
Naveen

#88 Prajakta on 01.13.14 at 11:45 am

Hi, pl. help me to learn about SQL, i am new to security field & eager to explore it.
my Email ID: prajakata.patil189@gmail.com