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

SWEN-331: Engineering Secure Software Benjamin S Meyers

VOTD: SQL Injection

Engineering Secure Software

Last Revised: September 3, 2020 1

SWEN-331: Engineering Secure Software Benjamin S Meyers

What is SQL Injection?

• Manipulating query strings to execute code
• Injecting SQL commands into query strings
• CWE-89

2

SWEN-331: Engineering Secure Software Benjamin S Meyers

Example

3

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

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

4

SWEN-331: Engineering Secure Software Benjamin S Meyers

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

5

SWEN-331: Engineering Secure Software Benjamin S Meyers

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

6

SWEN-331: Engineering Secure Software Benjamin S Meyers

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

7

SWEN-331: Engineering Secure Software Benjamin S Meyers 8

Source: https://cwe.mitre.org/top25/archive/2020/2020_cwe_top25.html

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