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 foo " SELECT user, pwd FROM users ” + " WHERE user= ‘ " + request.getParameter( " user " ) + ” ’ ”; SQL Query SELECT user, pwd FROM users WHERE user = ‘foo’

SQL injection ‘ OR 1=1# " SELECT user, pwd FROM users ” + " WHERE user= ‘ " + request.getParameter( " user " ) + ” ’ ”; SQL Query SELECT user, pwd FROM users WHERE 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 of 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 Next time • Basic techniques • More SQL injection • More advanced techniques • Cross-site scripting vulnerabilities (XSS) – E.g., UNION queries • Blind injection • Cross-site request forgery (CSRF) – Conditional responses – Time-based techniques 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