Wednesday, August 20, 2014
   
Text Size
Login

Basic Tests for SQL-Injection Vulnerabilities

What?
I'm putting this article here so that I can run through a checklist when I am adding an input field to an interface.

Why?
There is a strange complacency in many applications released for public use which are vulnerable to SQL Injection. According to industry reports over 60% of attacks on websites is through SQL-Injection alone.

How?
SQL-Injection is not as complicated as it is made out. An input field in a website form is where the hacker can add code that is submitted to the website and processed by the hosting server. Take a search engine for instance, this is a field which the end-user can freely enter any text they want, especially any code. The Google Search Engine has undergone a lot of work to prevent people from injecting code into their search function. But a search engine is just one example, on your website, you need to test EVERY input field the end-user can access. For an introduction on SQL-Injection, please read my article "Anti-SQL Injection Basics".

Tips
  • Avoid informative errors! For example: "error in script /mypath/myfile.php on line 81" are system errors which should NOT be displayed to the end-user as this can allow the hacker to examine the results and adapt their attack -> "Blind SQL-Injection" is a process of determining the structure of the database through error messages and server responses.

  • If you're expecting a number, then multiply by 1 My easiest fix but by no means a silver bullet, if your form submits a number and your receiving script expects a number, then multiply the submitted value by 1. In most cases, a string multiplied by 1 equals -1 (minus 1). Note: If a username is a number (eg. CustomerID=1234567890), test without apostrophes, eg. 1 or 1=1

  • Search-Engine apostrophes and quotes! End-users will want to use these in free-text so always check you account for this. (eg. Name=O'Reilly, SearchQuery="two words") I usually encode these temporarily between scripts to HTML or Numerical entities and then the results script would decode these.

Checklist
You may face legal/disciplinary action if you do not advise the administrators of the server/service you will be testing SQL-injection. Advise them that your account will need to be unlocked at a moments notice or for your IP address to be un-banned. I would not recommend being added to the whitelist in case this permission is accidentally replicated on a production environment!

The first input fields I like to test are the "Search Engine" and the "Login Form" of a website, most of the below examples are testing a "Login Form". You should aim to suppress any error messages and server responses in the production environment, leave the debugging to the developers. We will assume the receiving script has one of the worst SQL statements:

  1.  SELECT *  
  2.  FROM users  
  3.  WHERE username='<submitted_username>'  
  4.  AND password='<submitted_password>' 
* In some of the examples where I have placed the injection code in the password submitted value, this is for demonstration purposes as it is more likely that we would use the username field which may not get formatted as much (eg. password=MD5(submitted_password) but username is usually accent-sensitive, case-sensitive and stored as clear text in the database).


  1. Random SQL
    Type some random SQL for the input value and see if the server returns a message

    1.  Username:     SELECT Username FROM Users WHERE ID=1 
    2.  Password:     SELECT MD5(Password) FROM Users WHERE ID=1 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='SELECT Username FROM Users WHERE ID=1' AND password='SELECT MD5(Password) FROM Users WHERE ID=1' 
    » Result should be "invalid username/password". Suppress any other messages.

  2. Wildcards
    Type an asterisk (*) as the input value and observe

    1.  Username:     * 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='*' AND password='' 
    » Result should be "invalid username/password".

  3. Comments - dashdash
    Type a known username (eg. admin) as the input and suffix the comment command

    1.  Username:     admin'-- 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='admin'--' AND password='
    » Result should be "invalid username/password".

  4. Comments - hash
    Type a known username (eg. admin) as the input and suffix the comment command

    1.  Username:     admin'
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='admin'#' AND password='
    » Result should be "invalid username/password".

  5. Comments - bypassing pattern matches
    Testing if the target system is looking for keywords such as DROP and to avoid blacklists.

    1.  Username:     ';DR/**/OP tempTable; 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='';DROP tempTable;' AND password='' 
    » Result should be "invalid username/password".

  6. The Classic
    Type the following: ' or 1=1-- as the input value. Replace "admin" with a username you know exists.

    1.  Username:     admin 
    2.  Password:     ' or 1=1-- 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='admin' AND password='' OR 1=1--' 
    » Result should be "invalid username/password".
    More login tricks of this:

    1.  admin' --  
    2.  admin' #  
    3.  admin'/*  
    4.  ' or 1=1--  
    5.  ' or 1=1#  
    6.  ' or 1=1/*  
    7.  ') or '1'='1--  
    8.  ') or ('1'='1-- 


  7. Variations of the Classic: Comments
    Depending on your system, try inputting comment syntax. Replace "admin" with a username you know exists.

    1.  Username:     admin 
    2.  Password:     ' or 1=1 --IamJOE 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='admin' AND password='' OR 1=1 --IamJOE' 
    » Result should be "invalid username/password".

  8. Variations of the Classic: Empty
    Type the following: ' or ''=' as the input value (note that there is a space in front of the first apostrophe here). Replace "admin" with a username you know exists.

    1.  Username:     admin 
    2.  Password:      ' or ''=' 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='admin' AND password=' ' OR ''='
    » Result should be "invalid username/password".

  9. Variations of the Classic: NewLines
    Some scripts cannot interpret a newline, is it another query or does the script trim the last line submitted? Replace "admin" with a username you know exists.

    1.  Username:     admin 
    2.  Password:     ' 
    3.                OR 1=1-- 
    4.   
    5.  -- evaluates to: 
    6.  SELECT * FROM users WHERE username='admin' AND password='' 
    7.                OR 1=1--' 
    » Result should be "invalid username/password". New lines in SQL should be understood as \r\n.

  10. Variations of the Classic: URL Encoded
    This is the attack that often catches me out. Type the following: %27%20or%20%27%27%3D%27 as the input value. Replace "admin" with a username you know exists.

    1.  Username:     admin 
    2.  Password:     %27%20or%20%27%27%3D%27 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='admin' AND password='' OR ''='
    » Result should be "invalid username/password".

  11. Guest Password
    If you know of a valid username/password, check that your scripts do not validate on password alone.

    1.  Username:     Guest 
    2.  Password:     <Password you know exists in system> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='Guest' AND password='<known_password>' 
    » Result should be "invalid username/password".

  12. Lost users
    Type a random input value that the system is not expecting or that could not exist in the database.

    1.  Username:     '@#=6453kfe" 
    2.  Password:     <Leave Blank> 
    » Result should not give information as to how to validate your input (eg. "The specified username does not exist" or "Your username must consist of only letters and numbers"). Why does this matter? Here's a little game, try determining the username of the system administrator, the correct guess will more than often return "invalid password" (note there was no mention that the username was wrong or did not exist). The onus is on the web-developer to always return "invalid username/password" if there is any user/pass mismatch.

  13. Different User with UNION
    With UNION you can do cross-table SQL queries. In other words, you can make a query return records from another table. Here we're just adding a row of data into the mix:

    1.  Username:     <Leave Blank> 
    2.  Password:     ' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1--  
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='' AND password='' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1-- ' 
    » Result should be "invalid username/password". Any other response should be addressed.

  14. WHERE Not In
    Quite a common attack due to its simplicity and effectiveness (used mostly for Blind SQL Injection).

    1.  Username:     ' AND users NOT IN ('First User', 'Second User');-- 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='' AND users NOT IN ('First User', 'Second User');--' AND password='' 
    » Result should be "invalid username/password". Any other response should be addressed.

  15. WHERE Not Exist
    I haven't seen this in use but the logic from the previous example remains.

    1.  Username:     ';SELECT TOP 1 name FROM members WHERE NOT EXIST(SELECT TOP 0 name FROM members)  
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='';SELECT TOP 1 name FROM members WHERE NOT EXIST(SELECT TOP 0 name FROM members)' AND password='' 
    » Result should be "invalid username/password". Any other response should be addressed.

  16. Stacked
    Testing to see that we cannot execute more than one query in one transaction:

    1.  Username:     admin';DROP myTable-- 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='admin';DROP myTable--' AND password='
    » Result should be "invalid username/password". Any other response should be addressed.

  17. Version divide by zero
    The following comment allows you to determine what SQL version the target is running. If you get a "divide by zero" error, a) you know it works and b) the site is vulnerable:

    1.  Username:     /*!32302 1/0, */  
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='/*!32302 1/0, */' AND password='' 
    » Primarily used for Blind SQL Injection, result should be "invalid username/password". Any other response should be addressed.

  18. IF statements
    Blind SQL Injection is the process of determining the structure of the database through error messages or in this example, IF statements here are used to return a true/false, 1/0, or yes/no result:

    1.  Username:     <Leave Blank> 
    2.  Password:     ';IF((SELECT user) = 'sa' OR (SELECT user) = 'dbo') SELECT 1 ELSE SELECT 1/0;-- 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='' AND password='';IF((SELECT user) = 'sa' OR (SELECT user) = 'dbo') SELECT 1 ELSE SELECT 1/0;--' 
    » Throws a divide by zero error if current logged user is not sa or dbo. Result should be "invalid username/password". Any other response should be addressed.

  19. Wait for Delay
    for Blind SQL Injection, can be used for testing true/false conditions or if vulnerable, can make your server quite busy.

    1.  Username:     ';waitfor delay '0:0:10'--  
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='';waitfor delay '0:0:10'--' AND password='' 
    » Result should be "invalid username/password". Any other response should be addressed.

  20. DataType Conversions
    Type a conversion:

    1.  Username:     CAST('username' AS SIGNED INTEGER) 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='CAST('username' AS SIGNED INTEGER)' AND password='' 
    » Result should be "invalid username/password". Any other response should be addressed.

  21. Determining Column DataTypes
    We are checking the error messages you would get for this as it will allow anyone using Blind SQL Injection to find out more about the database structure:

    1.  Username:     ' UNION SELECT SUM(columntofind) FROM users-- 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='' UNION SELECT SUM(columntofind) FROM users--' AND password='' 
    » Result should be "invalid username/password". Any other response should be addressed. Warning: Microsoft will return "...sum or average operation cannot take a varchar...".

  22. Counting Columns with Order By
    If we are able to run other SQL queries, then the basic column counting process is using the ORDER BY #:

    1.  Username:     <Leave Blank> 
    2.  Password:     ' ORDER BY 1;-- 
    3.  ... 
    4.  Username:     <Leave Blank> 
    5.  Password:     ' ORDER BY 2;-- 
    6.  ... 
    7.  Username:     <Leave Blank> 
    8.  Password:     ' ORDER BY N;--       
    9.   
    10.  -- evaluates to: 
    11.  SELECT * FROM users WHERE username='' AND password='' ORDER BY 1;--' 
    » If you get an error, then you have determined the number of columns. Result should be "invalid username/password". Any other response should be addressed.

  23. Second Order
    Basically you insert some SQL Injection in one place and expect it to be unfiltered in another action. This is a common hidden layer problem:

    1.  Username:     ' + (SELECT TOP 1 username FROM users ) + '  
    2.  Password:     ' + (SELECT TOP 1 password FROM users ) + ' 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='' + (SELECT TOP 1 username FROM users ) + '' AND password='' + (SELECT TOP 1 password FROM users ) + '
    » Result should be "invalid username/password". Any other response should be addressed.

  24. MS SQL Server: shutdown
    This should be disabled by default but it's quite an important test. NB: Do not run as a DB Administrator.

    1.  Username:     ';SHUTDOWN --  
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='';SHUTDOWN --' AND password='' 
    » Result should obviously not allow this.

  25. MS SQL Server: get user defined objects
    for Blind SQL Injection, hackers will use this to examine user defined tables, etc. NB: Do not run as a DB Administrator.

    1.  Username:     ';SELECT name FROM sysobjects WHERE xtype = 'U';-- 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='';SELECT name FROM sysobjects WHERE xtype = 'U';--' AND password='' 
    » Result should be "invalid username/password". Any other response should be addressed.

  26. MS SQL Server: get column names
    for Blind SQL Injection, watch out for testing true/false conditions. NB: Do not run as a DB Administrator.

    1.  Username:     ';SELECT name FROM syscolumns WHERE id =(SELECT id FROM sysobjects WHERE name = 'known_table_name');-- 
    2.  Password:     <Leave Blank> 
    3.   
    4.  -- evaluates to: 
    5.  SELECT * FROM users WHERE username='';SELECT name FROM syscolumns WHERE id =(SELECT id FROM sysobjects WHERE name = 'known_table_name');--' AND password='' 
    » Result should be "invalid username/password". Any other response should be addressed.

  27. MS SQL Server: xp_cmdshell
    The requirement here is to suppress any information that will say this command executed. NB: Do not run as a DB Administrator.

    1.  EXEC master.dbo.xp_cmdshell 'ping <known_ip_address>' 
    » Result should suppress informative messages.

  28. Login and Login
    Login as a standard user, and then try to login again without logging out in between.

    1.  Workaround #1: Review your application session caching. 
    2.  Workaround #2: Hide login link once logged in. 
    » Result should fail if system is set up properly and advise you you are already logged in.

  29. Login/Logout/Login
    Do the following in as little time as possible:

    1.  Login, Logout, Login, Logout, Login, Login 
    » Result should be the same as the previous test but we are testing session caching here.

  30. Correct ID
    Enter a valid value and note the number of rows and what messages are returned.

    1.  Also: ${Test Request: Login#Response#//sam:loginResponse[1]/sessionid[1]} 
    » Result should work as per usual.

  31. Expired Session
    Login while logged out on the same day or when sessions are suspected for not changing.

    1.  Also test closing the browser and re-opening, then login. 
    » Result should show new session and nothing conflicting.

  32. Expired Multiple Sessions
    Try logging into the application with two instances of the viewer. In other words, we want the same user using 2 separate browsers looking at the same site - NB: not just two different "browser tabs" as major browsers tend to consolidate session details under each instance.

    1.  Open two browser instances without using one to open the other. 
    2.  See if you can login to both instances. 
    3.  -- further: see if you can login to the website on 2 separate computers 
    » Result should error or deal appropriately with two sessions by the same user. Why does this matter? If your web-application only has 10 licenses for example, and an end-user can create more than one instance, if they create more than 10 then does your web-application stop working?

  33. Lockout
    If this is a login input, then test how many times you can try incorrect logins without getting locked out. If this is merely a search feature, test how many queries you can enter before being blocked (this may require a bot).

    1.  Login as many times with invalid credentials until you are locked out. 
    » Result is you should be locked out of the system or be unable to execute further processes.

  34. Element Duplication
    If you can modify the URL, try submitting the same input field twice in one url (specify the parameter twice)

    1.  eg.  /Login.php?username=myUsername&password=MyPassword&username=admin 
    » Result should demonstrate that the system can still understand the input and ignore the additional elements.

  35. Element Ommission
    Again if you can modify the URL, submit less than the expected input values (so if username&password required just send username).

    1.  eg.  /Login.php?username=myUsername 
    » Result should demonstrate that the system will advise on missing elements.

  36. Malformed XML
    Need to create a posting form on a server that can communicate with the service you are testing. Create some XML file with invalid XML

    1.  <login><username>joe&joe</username></login> 
    » Results should have no messages. Any error/validation messages should be suppressed in this case.

  37. The weirdest things happen with the weirdest content
    If username is expected to be 25 characters, enter 26. If free-text field, then enter text of about 1000 lines.

    1.  y?2`Fg0y1&&qiJH`Gl<p5hRZp<g(ICZ{3<)(..:e|<xgWI9V,k4s<.[L-6p}WgZ]~h 
    2.      or copy & paste a Lorem Ipsum text (http://www.lipsum.com/) 
    » Result should have no error messages or possibly a notification of a size limit. Lookout for timeouts and size limits as a giveaway for hackers.

  38. Xpath Injection variants
    This is similar to SQL injection but the SQL is parsed and escaped among XML code which is used between many of our applications.

    1.  eg. <login>string(//user[username/text()='' or '1' = '1' and password/text()='' or '1' = '1'])</login> 
    » Result should be no messages or errors.

  39. Hex-based Injection
    for MySQL: If successful this will show the contents of c:\boot.ini:

    1.  LOAD_FILE(0x633A5C626F6F742E696E69) 
    2.   
    3.  or  
    4.   
    5.  LOAD_FILE(HEX('c:\boot.ini')) 
    » Result should suppress informative messages.

  40. SQL-Injection via the URL: Method GET
    So do you have a question mark in your website address? The values that follow it will be data that a page will process, load all of the pages up and play with the values bearing in mind dynamic variables:

    1.  Base Model:     http://www.mysite.com/product.asp?id=4  
    2.  Test #1:     http://www.mysite.com/product.asp?id=5-1   
    3.  Test #2:     http://www.mysite.com/product.asp?id=4 OR 1=1   
    4.  Test #3:     http://www.mysite.com/product.asp?name=Bo’%2b’ok   
    5.  Test #4:     http://www.mysite.com/product.asp?name=Bo’ || ’ok   
    6.  Test #5:     http://www.mysite.com/product.asp?name=Book’ OR ‘x’=’x   
    7.   
    8.  Base Model:     http://www.mysite.com/query.php?user=4  
    9.  Test #1:     http://www.mysite.com/query.php?user=4 OR 1=1  
    10.  Test #2:     http://www.mysite.com/query.php?user=1+union+select+load_file(0x63...),1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1          // Read File 
    » Results should lead to the same product and suppress informative messages.

  41. While we're here: Cross-Site Scripting (XSS)
    The same fields you are testing can be vulnerable to XSS-Injection as well: Go to a field and type the following

    1.  <script>alert("XSS");</script> 
    » If vulnerable: Will popup a message saying "XSS". Warning If not vulnerable, will submit a command that the system admins will know is an attempt of XSS-Injection!

Source(s):

Comments   

Steve Moyer
# Steve Mon, 3rd September 2012
I'm using MediaWiki and someone managed to inject 11 gigabytes of data into our MySQL databases through the site. Is there a fix for this problem?
Like | Dislike | 0 Reply | Reply with quote | Quote
# Webmaster Tue, 11th September 2012
Hi Steve,

We had some breakins but nothing like that. Was it 11Gb of ASCII files or something binary? It's surprising because MediaWiki has a default installation which blocks most uploads. Did you find where they got through? (Our case was that they got through using URL encoded entities).
Like | Dislike | 0 Reply | Reply with quote | Quote
MohammadReza
# MohammadReza Sun, 1st September 2013
nice :-*
Like | Dislike | -1 Reply | Reply with quote | Quote
kumar prakash
# kumar Thu, 17th October 2013
Thanks for sharing this information.

Thanks
Like | Dislike | +1 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Joes Word Cloud (JWC)

    • Tue 19-Aug-14
      Hi Joe, Thank you for your prompt reply. Below, an "image" of the cloud on my website : "pour ante ...
      Nathalie  
    • Mon 18-Aug-14
      Hi Nathalie, Thanks for the message. The module does not see Virtuemart text but that sounds like ...
      Webmaster  
    • Mon 18-Aug-14
      Hi Joel, Fist of all, thank you for the module. What such a work ! I have a problem with Virtuemart module.
      Nathalie  
    • Mon 18-Aug-14
      Hi there, glad to hear it's working. Yes, the exclude list needs to be words separated by commas ...
      Webmaster  
    • Mon 18-Aug-14
      That's it. Thanks. I had typed in category names, not realising what the numbers were for. Now I have ...
      mrinterpreter