Lecture 20: SQL Injection

• Slides
• Playlist (length: 41:49)

Intro to Web Security Attacks

Code Injection

What does each part of the special input 2+3); system('rm *.*' do? Why don’t we include a closing parentheses at the end?

2+3 is our dummy input to eval.

); ends the eval command. This shouldn’t be allowed, but since we are treating all input as code, the backend now thinks the eval command is over.

system('rm*.*' runs our malicious ‘remove everything’ command. We don’t need a closing parentheses, because the eval function already adds a closing parentheses after our input.

Intro to SQL Injection

SQL Review

Note: If you feel comfortable with basic SQL (SELECT statements, WHERE clauses, inserting and deleting entries from tables, DROP TABLES), feel free to skip this video and refer back to it as needed.

Consider the Customer table with AcctNum, Username, and Balance fields. Write a query to output the usernames of all accounts with balance greater than or equal to 10.

SELECT Username FROM Customer WHERE Balance >= 10

SQL Injection Example

What SQL query is executed when the attacker inputs alice'; SELECT * FROM Customer;'? Why is each part of this input necessary to avoid a syntax error?

The resulting query is SELECT AcctNum FROM Customer WHERE Username='alice'; SELECT * FROM Customer;''

alice is the dummy input to the original query.

' closes the first quote added by the backend code.

; ends the SQL statement and starts a new one.

SELECT * FROM Customer; is the malicious statement we want to execute.

' matches with the second quote added by the backend code. Otherwise, the SQL interpreter would error on an unmatched quote.

Real-world SQL Injection Attacks

Another SQL Injection Example

Can an attacker exploit this query to learn the password of the admin user? If yes, write a malicious input that would leak the password. If no, explain why.

No. This SQL query only returns ‘success’ or ‘failure’ to the web browser, so an attacker can’t see the output of the SQL query. However, the attacker can still do a lot of damage, as shown in the video.

Defense: Input Escaping

Consider an escaping function that takes user input and replaces all instances of a single quote ' with the escaped version \'. Can an attacker still craft a malicious input using a single quote? If yes, write a malicious input that would bypass this escaping function. If no, explain why.

Yes. Consider the following input: alice\';

After passing through the escaper, the input is now alice\\'

SQL interprets the first backslash as escaping the second backslash, so the literal input it sees is alice\' (alice, followed by a backslash, followed by a single quote). Now the single quote is no longer escaped!

An important takeaway is that correctly escaping all possible inputs is very hard.

Defense: Parameterized SQL

(True/False) Parameterized SQL defends against all SQL injection attacks.

True. Using parameterized SQL prevents any user input from being treated as code.

Conclusion

After finishing this lecture, you should be able to complete Q2 on Homework 6.