DAT152 - Advanced Web Applications - SQL Injection PDF

DAT152 – Advanced Web Applications SQL Injection  Agenda Statistics Background SQL injections SQLi Mitigations  OWASP Top 10 Security Risks OWASP Top-10 Web App Vulnerabilities A3 – Injection (A1 – 2017) – SQL Injection – Command Inj...

DAT152 – Advanced Web Applications SQL Injection  Agenda Statistics Background SQL injections SQLi Mitigations  OWASP Top 10 Security Risks OWASP Top-10 Web App Vulnerabilities A3 – Injection (A1 – 2017) – SQL Injection – Command Injection – XXE Injection – XPath Injection – LDAP Injection – Log Injection – Http Parameter pollution – etc https://owasp.org/www-project-top-ten/ Statistics SQL injection statistics Background SQL injection SQLi Mitigation Statistics SQL injection statistics Background SQL injection SQL injection vulnerability found in Trusted Tools Free Music v.2.1.0.47, v.2.0.0.46, v.1.9.1.45, v.1.8.2.43 allows a remote attacker to cause a denial of service via the search history table SQLi Mitigation Statistics SQL injection statistics Ransomware Attack caused by SQL Injection Background July 02, 2021 – Kaseya VSA Server The attack was triggered via an authentication bypass vulnerability in the Kaseya VSA web interface. SQL injection SQLi Mitigation RMM – Remote Monitoring and Management Statistics SQL Interfaces Web applications are mostly glorified database Background applications Programmers interface the web app to database via e.g., SQL (Structured Query Language) SQL injection Username SQLi username password Password internet alice Mitigation bob tom Login Statistics SQL interfaces & Untrusted data Source and Sink – Source: Entry point where external data enters the application Background – Sink: Exit point where such data is consumed/processed by the application If the source of the input data is untrustworthy, then data is said to be tainted SQL injection – Web parameters and cookies – Data from files, Data from databases – Data from web services SQLi – Environment variables Mitigation – Open ports If tainted data reaches a sensitive sink, a security issue may exist – e.g., a SQL engine Statistics SQL - Attack surface (Web App) GET http://localhost:9090 HTTP/1.1 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:68.0) Gecko/20100101 HTTP header info Firefox/68.0 Background Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8 – e.g. Referrers, etc. Accept-Language: en-US,en;q=0.5 Referer: http://localhost:9090/OWASPTopVulnerability/CommandInjection Form fields Connection: keep-alive Cookie: username=test1; role=user; JSESSIONID=BC48897440437F27C7508892394414C4 – Textarea, buttons, Upgrade-Insecure-Requests: 1 password, text, check SQL injection Cache-Control: max-age=0 Host: localhost:9090 boxes, etc Cookies, files, databases, image, url, SQLi css, page1 page2 Open ports Statistics SQL Commands Data Control Language (DCL) Background – GRANT, REVOKE Data Definition Language (DDL) SQL injection – CREATE, ALTER, DROP, TRUNCATE Data Query Language (DQL) – SELECT SQLi Mitigation Data Manipulation Language (DML) – INSERT, UPDATE, DELETE Statistics SQL Injection SQL Injection occurs when untrusted data are Background added to database queries to change the application’s design SQL injection Dynamic string building when programmers mix query language fragments and untrusted data SQLi String query = “SELECT id, name, short_name FROM company WHERE id = “ + request.getParameter(“company_id”); Mitigation The injection vector here is from the request.getParameter() which is determined at runtime!!! See: https://www.websec.ca/kb/sql_injection Statistics SQL Injection Background First-Order SQLi – Adds unsafe user input and processes it immediately SQL injection Second-Order SQLi – Stores unsafe user input for future use and later SQLi uses this unsafe input with SQL query Mitigation – Also known as stored SQL injection Statistics SQLi - Impact A successful SQL injection exploit can: Background Read and modify sensitive data from the database Execute administrative operations on the database – Shutdown auditing or the DBMS SQL injection – Truncate/drop tables and logs – Add/delete users SQLi Recover the content of a given file present on the Mitigation DBMS file system Issue commands to the operating system Statistics SQLi - Impact Confidentiality, Integrity, Availability, Authentication, Authorization, Auditing (Non-Repudiation) Background Allows attackers to – Spoof identity SQL injection – Tamper with existing data – Cause repudiation issues such as voiding transactions or SQLi changing balances Mitigation – Allow the complete disclosure of all data on the system – Destroy the data or make it otherwise unavailable – Become administrator of the database server Statistics SQLi – Common Attacks Retrieving can modify an SQL query to return hidden data additional results. Background Subverting can change a query to interfere with application logic the application's logic. SQL injection can retrieve data from different UNION attacks database tables. Examining can extract information about the SQLi the database version and structure of the database. Mitigation Blind SQL results of a query you control are not injection returned in the application's responses. Statistics SQLi – Retrieving hidden data An employee can only see his record stored in a MySQL db when logged in to the system Background A ‘curious’ employee wants to see other people’s salary. SQL injection – The system has an interface where he can query the record with his userid public void getUserSalary (String user) { String query = "SELECT * FROM sal_table WHERE SQLi FirstName='"+user+"’"; Mitigation Statement stmt = conn.createStatement(); stmt.execute(query); ResultSet rs = stmt.getResultSet(); } A demo with a payload: user = “test’ or ‘1’=‘1’– “ Statistics SQLi - Examples An employee can only see his record stored in a MySQL db when logged in to the system Background A curious employee wants to see other people’s salary. SQL injection – Once logged in, he can only see his userid from where he can request for his salary record. SQLi Mitigation How can we bypass this? Statistics SQLi - Examples How can we bypass this? One way is it to use a proxy between the web client and the web Background server The proxy accepts request packets from the client and forwards to the server, get the response from the server and forwards to the client SQL injection SQLi Mitigation Statistics SQLi - Examples How can we bypass this? Use a proxy between the web client and the web server Background The proxy accepts request packets from the client and forwards to the server, get the response from the server and forwards to the client SQL injection SQLi Mitigation Lesson #0: Validate at the server side: NEVER rely on client side validation for critical functions Statistics SQLi – Subverting application logic An attacker wants to bypass an authentication Background system SQL injection public String authenticateWithSalt(String userid, String password) { String query0 = "SELECT salt FROM owasp_users WHERE username = '"+userid+"’"; String salt = getSalt(query0); String passhash = null; SQLi try { byte[] saltbytes = DatatypeConverter.parseHexBinary(salt); PasswordHash ph = new PasswordHash(PasswordHash.SHA256); Mitigation passhash = ph.generateHashWithSalt(password, saltbytes); } catch (NoSuchAlgorithmException e) { //e.printStackTrace(); } String query = "SELECT * FROM owasp_users WHERE username = '"+userid+"’ AND password = '"+passhash+"'"; username = “test1’ or 1 = 2-- ” Statistics SQLi – Subverting application logic An attacker registers as a new user but Background changes the administrator’s password SQL injection SQLi Mitigation Statistics SQLi – Union Attack Example: Probe the user’s database to retrieve other users login credentials Background SELECT * FROM sal_table WHERE FirstName = ‘" + untrusteddata untrusteddata = test1' UNION SELECT id, username, password, salt, 1 FROM owasp_users-- SQL injection SQLi Mitigation Statistics SQLi – Examining the database Purpose: Gather some info about the db Background – Type of DB – Version of DB engine SQL injection – Tables in the DB – Columns in the tables SQLi SELECT @@version— SELECT * FROM information_schema.tables Mitigation SELECT * FROM information_schema.columns WHERE table_name = 'Users' Statistics Blind SQLi Blind SQL injection arises when an Background application is vulnerable to SQL injection, but its HTTP responses do not contain the results SQL injection of the relevant SQL query or the details of any database errors. SQLi Use case: Retrieving information (e.g., Mitigation password) Statistics Blind SQLi Assume a book cart where you can check Background whether a displayed book is available or not – If available, a “Yes” is displayed SQL injection – If unavailable. Nothing is shown SQLi Mitigation Statistics Blind SQLi Payloads Background – Cart.jsp?bookid=1 AND 1=1 Will evaluate to true (Yes is displayed) SQL injection – Cart.jsp?bookid=1 AND 1=2 Will evaluate to false (Nothing is displayed) SQLi Mitigation Statistics Blind SQLi We can use this behaviour to exploit blind SQLi and retrieve Background admin or a user’s password Assumption: – We know the database table we want to attack SQL injection First, we may determine the length of the password for a given username that we guess e.g., – "1 AND 1=(SELECT 1 FROM owasp_users WHERE username='admin' AND length(password) > 40)-- " = false (nothing SQLi – is displayed) "1 AND 1=(SELECT 1 FROM owasp_users WHERE username='admin' AND length(password) < 40)-- " = false(nothing is displayed) Mitigation – "1 AND 1=(SELECT 1 FROM owasp_users WHERE username='admin' AND length(password) = 40)-- " displayed) = true (Yes is – Or – "1 AND 1=(SELECT 1 FROM owasp_users WHERE username='admin' AND length(password) < 41)-- " = true (Yes is displayed) Statistics Blind SQLi for (i=1 to 40){ Background – for(j=0 to alphanum.length-1){ char = alphanum[j] "1 AND 1=(SELECT 1 FROM owasp_users WHERE username='admin' AND substr(password, i,1) = char')-- " SQL injection if(result.query == true){ – save(char) – break; SQLi } Mitigation –} } String[] alphanum = {"A", "B", "C", "D", "E", "F","0","1","2","3","4","5","6","7","8","9"}; Statistics Blind SQLi Suppose the application performs the same query Background but does not behave differently (no visible response can be inferred) Can induce conditional responses by triggering SQL injection SQL errors Can trigger time delays xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)=‘a SQLi xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, Mitigation 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a '; IF (1=2) SLEEP(10)–- ‘; IF (1=1) SLEEP(10)-- '; IF (SELECT COUNT(Username) FROM Users WHERE Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') = 1 SLEEP(10)'-- Statistics SQLi - Mitigations Primary defenses Background Option 1: Use of Prepared Statements (with Parameterized Queries) Option 2: Use of Properly Constructed Stored Procedures SQL injection Option 3: Allow-list Input Validation Option 4: Escaping All User Supplied Input SQLi Additional defenses Mitigation Enforcing Least Privilege Performing Allow-list Input Validation as a Secondary Defense https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html Statistics SQLi - Mitigations Option 1: Parameterized query – Most reliable technique Background – Parameterization is a mechanism where query data is separated from query structure – Separates command/code from data SQL injection – In Java, this is provided via the: java.sql.PreparedStatement SQLi String query = "SELECT * FROM sal_table WHERE FirstName=?"; Mitigation PreparedStatement pstmt = conn.prepareStatement(query); pstmt.setString(1, userInput); ResultSet rs = pstmt.executeQuery(); Query is prepared first and binds data later Hibernate: insert into author (firstname,lastname,author_id) values (?,?,?) Hibernate: select a1_0.author_id,a1_0.firstname,a1_0.lastname from author a1_0 where a1_0.author_id=? Statistics SQLi - Mitigations Option 2: Stored Procedures Background SQL injection SQLi When used properly = parameterized query Mitigation Statistics SQLi - Mitigations Option 3: Allow-list Input Validation Background Bind variables cannot be used in some parts of SQL queries e.g., Table or column names SQL injection SQLi Mitigation Statistics SQLi - Mitigations Option 4: Escaping All User-Supplied Input o Escape all user supplied input using the proper escaping scheme Background for the database you are using String username = "victim' OR 1=1 -- "; SQL injection String sql = "SELECT * FROM SecOblig.AppUser WHERE username = '" + username + "' AND passhash = '" + hashedPassword + "'"; SQLi SELECT * FROM SecOblig.AppUser WHERE username = 'victim' OR Mitigation 1=1 -- ' AND passhash = ''; escape(username) = "victim\' OR 1=1 – "; Query will fail SELECT * FROM SecOblig.AppUser WHERE username = 'victim '' OR 1=1 -- ' AND passhash = ''; Statistics SQLi – Mitigations (Additional) Least privilege: minimize the privileges Background assigned to different database accounts (avoid DBA/admin access rights to all accounts) Verify the number of actual results to SQL injection expected results Type safely: If you’re expecting a date object, SQLi force the result into a date object Mitigation Statistics SQLi – Mitigations (Extra) Question: Does a prepared statement always prevent Background against an SQL injection? – Answer: No SQL injection – e.g., If user input is used to provide sorting (“order by” clause) SQLi Mitigation Statistics SQLi – Mitigations (Extra) Order by Background SecOblig.History table has the following columns and datatypes. o datetime TIMESTAMP o username VARCHAR(50) o searchkey VARCHAR(50) SQL injection SQLi Mitigation public List getSearchHistoryForUser(String username, String sortkey){ String sql = "SELECT * FROM SecOblig.History WHERE username = '" + username + "' ORDER BY "+sortkey+" ASC"; return getSearchItemList(sql,50); }  Next Lecture Broken Authentication I