CIS 4615 meeting -*- Outline -*- * SQL injection Based on chapter 1 of the book: 24 Deadly Sins of Software Security by M. Howard, D. LeBlanc, and J. Viega (McGraw-Hill, 2010). ** overview ------------------------------------------ WHAT IS A SQL INJECTION ATTACK? 1. Database App 2. Attacker 3. App uses In Java: public static boolean doQuery(String id) { /* ... */ ResultSet rs = st.executeQuery( " SELECT ccnum FROM cust " + " WHERE id = " + id); /* ... */ } ------------------------------------------ ... asks for input from user ... supplies input ... the input in a SQL query (via string concatenation) Q: Why is this code a problem? Because if the input Id is 1 or 2>1 -- (where -- is the comment operator) then the query will return all ccnums! ------------------------------------------ SERIOUSNESS OF SQL INJECTION ATTACKS #1 flaw on the OWASP top 10 list (2013). - easy to exploit - severe impact Can put "all data in a database at risk." Can lead to legal liability: - Calif.'s online privacy protection act - Germany's Fed. Data Protection Act - Sarbanes-Oxley act (in US) must protect data used to derive company financial statements - PCI data security standard (DSS) - HIPPA ------------------------------------------ ** generalization ------------------------------------------ DOES IT ONLY APPLY TO SQL? ------------------------------------------ ...No! Any interpreter can be at risk 1. Takes user input as a string 2. passes string to interpreter LDAP, Xpath, or NoSQL queries; OS commands; XML parsers, SMTP Headers, program arguments, etc. ------------------------------------------ A CAREFUL PERSPECTIVE Limit user input to the smallest Validate user input by Form a more complex query by ------------------------------------------ ... syntactic category that makes sense e.g., a number ... parsing to see that it falls in that category ... splicing the parsed input into the abstract syntax tree for the query (like a prepared statement). This is also (marginally) faster! *** tainting ------------------------------------------ TAINTING A static analysis that tracks values derived from String i; i = input(); String j = i; runit(j); void runit(String id) { String k = myQuery + id; st.executeQuery(k); } ------------------------------------------ ... user input(s) Q: is k tainted? yes! ------------------------------------------ TAINTING & CONSERVATISM String i; i = input(); String j; if (cond()) { j = i; } else { j = "27"; runit(j); runit("32"); void runit(String id) { String k = myQuery + id; st.executeQuery(k); } ------------------------------------------ Q: Is the executeQuery call sinful? Yes, id can be tainted, and thus k Explain that this is conservative (an overapproximation), since the second call to runit doesn't have this problem. ------------------------------------------ GENERALIZTION Should only compute with data that User inputs don't! Tainted data = ------------------------------------------ ... has integrity ... no integrity ** Prevention ------------------------------------------ PREVENTION OVERVIEW Tools: - CAT.NET for MS .NET work, an add-on to Visual Studio - Perl taint checking (perl -T) Best: Code reviews focusing on SQL injection Poor: Look for specific attacks in input ------------------------------------------ Q: Why is looking for specific attacks poor? The list may not be complete... Q: What happens if you remove "delete" from "deldeleteete"? You still get "delete" *** steps ------------------------------------------ PREVENTION STEPS Never trust Do NOT form queries using Use prepared/parameterized SQL statements In Java: public static boolean doQuery(String arg) { Pattern p = Pattern.compile("^\\d{1,8}$"); if (!p.matcher(arg).find()) { return false; } /* ... */ PreparedStatement st = con.prepareStatement( "exec pubs..sp_GetCreditCard ?"); st.setString(1, arg); ------------------------------------------ ... user input ... user input, must validate it! Q: What does it mean to validate a string? Limiting it to some class of tokens. ... string concatenation or string replacement. Q: What security service does trusting user input violate? Integrity! Q: What does that pattern in the Java code mean? it matches a string that is only 1-8 digits *** other defenses ------------------------------------------ DEFENSE IN DEPTH Encrypt data that is: sensitive, PII, or confidential Access control: - deny access - only allow access - use least privilege Do not embed Do not store ------------------------------------------ ... to underlying database objects ... to stored procedures and views ... to access DB (not root!) ... the login password in the application or the connection string ... database configuration information in the web directory