VOTD: SQL Injection Engineering Secure Software Last Revised: - PowerPoint PPT Presentation

VOTD: SQL Injection Engineering Secure Software Last Revised: September 3, 2020 SWEN-331: Engineering Secure Software Benjamin S Meyers 1

What is SQL Injection? Manipulating query strings to execute code ● Injecting SQL commands into query strings ● CWE-89 ● SWEN-331: Engineering Secure Software Benjamin S Meyers 2

Example private static String auth(String user, String pwd, Connection conn) throws SQLException { ResultSet resultSet; resultSet = conn.createStatement().executeQuery( "SELECT * FROM Users WHERE Username='" + user + "' AND Password='" + pwd + "'"); // BAD ^^^^^^ BAD ^^^^^ if (resultSet.next()) // any rows? return "Authenticated!!"; else return "Not authenticated!!"; } SWEN-331: Engineering Secure Software Benjamin S Meyers 3

How Do You Do It? Line Comments: -- ● SELECT * FROM members WHERE username = ‘admin’--’ AND password = ‘password’ This can be used to bypass passwords and login as admin ○ Inline Comments: /* */ ● DROP ‘/*comment*/tablename’ This can be used to bypass blacklisting ○ And lots of other ways ● SWEN-331: Engineering Secure Software Benjamin S Meyers 4

Example Executing arbitrary OS commands ● SQL 9.3+ ○ Any user with the pg_execute_server_program role can ○ execute arbitrary OS commands ○ COPY cmd_exec FROM PROGRAM `cat /root/.ssh/id_rsa` ○ COPY cmd_exec FROM PROGRAM `echo ‘ben ALL=(ALL:ALL) ALL’ >> /etc/sudoers` User roles ○ SWEN-331: Engineering Secure Software Benjamin S Meyers 5

Mitigations Prepared statements with binding variables ● Instead of: ○ SELECT Name, Salary FROM Employee WHERE Salary > *user_input*; Do: ○ String query = "SELECT Name, Salary FROM Employee WHERE Salary > ?"; PreparedStatement pstmt = conn.prepareStatement(query); pstmt.setInt(1, *user_input*); ResultSet rs = pstmt.executeQuery(); Escaping characters is a poor substitute (e.g. character sets) ● OO-relational mappers can mitigate some SQL injections, but ● they’re not foolproof SWEN-331: Engineering Secure Software Benjamin S Meyers 6

Notes Applicable to most programming languages that can execute ● SQL: Java, Ruby, PHP, etc. Not particularly hard to fix, you just have to know to fix it ● Some people will tell you that you need lots of tools to fix SQL ● injection -- that’s a lie, just use prepared statements History and consequences of SQL-Injection ● SWEN-331: Engineering Secure Software Benjamin S Meyers 7

Source: https://cwe.mitre.org/top25/archive/2020/2020_cwe_top25.html SWEN-331: Engineering Secure Software Benjamin S Meyers 8

Source: https://xkcd.com/327/ SWEN-331: Engineering Secure Software Benjamin S Meyers 9