Software Security - SQL Injection

Software Engineering
1.0x

Software Security - SQL Injection

Created 2 years ago

Duration 0:09:12
lesson view count 346
Select the file type you wish to download
Slide Content
  1. Software Security: SQL Injection

    Slide 1 - Software Security: SQL Injection

    • Emerson Murphy-Hill
  2. SQL Injection - String Type Example

    Slide 2 - SQL Injection - String Type Example

    • String query = “SELECT * FROM users WHERE login = ‘” + login + “’ AND password = ‘” + password + “’”;
    • Login:
    • Password:
    • Expected input:
    • SELECT *FROM users
    • WHERE login = ‘John’ AND password = ‘John1234 ’
    • Result: Returns John’s user information
    • John
    • John1234
  3. SQL Injection - String Type Example

    Slide 3 - SQL Injection - String Type Example

    • String query = “SELECT * FROM users WHERE login = ‘” + login + “’ AND password = ‘” + password + “’”;
    • Login:
    • Password:
    • Expected input:
    • SELECT *FROM users
    • WHERE login = ‘’ OR ‘1’=‘1’
    • AND password = ‘’ OR ‘1’=‘1’
    • Result: Returns all user information in the users table
    • ‘ OR ‘1’ = ‘1
    • ‘ OR ‘1’ = ‘1
  4. SQL Injection – Date Type Example

    Slide 4 - SQL Injection – Date Type Example

    • Submitting SQL query logic instead of a valid date can expose confidential records.
    • .
    • From: www.itsa.ufl.edu/2006/presentations/malpani.ppt
  5.  String query = “SELECT * FROM accounts        WHERE username = ‘” + strUName + “’	 AND tran_date >= ‘” + strSDate + “’ 	 AND tran_date <= ‘” + strEDate + “’”;

    Slide 5 - String query = “SELECT * FROM accounts WHERE username = ‘” + strUName + “’ AND tran_date >= ‘” + strSDate + “’ AND tran_date <= ‘” + strEDate + “’”;

    • Expected input:
    • SELECT *FROM accounts
    • WHERE username = ‘John’
    • AND tran_date >= ‘2005-01-01’
    • AND tran_date <= ‘2006-06-28’
    • Result: Returns John’s transactions between given dates
    • SQL Injection – Date Type Example
  6. SQL Injection – Date Type Example

    Slide 6 - SQL Injection – Date Type Example

    • Submitting SQL query logic instead of a valid date can expose confidential records.
    • .
    • Modified from: www.itsa.ufl.edu/2006/presentations/malpani.ppt
  7.  String query = “SELECT * FROM accounts        WHERE username = ‘” + strUName + “’	 AND tran_date >= ‘” + strSDate + “’ 	 AND tran_date <= ‘” + strEDate + “’”;

    Slide 7 - String query = “SELECT * FROM accounts WHERE username = ‘” + strUName + “’ AND tran_date >= ‘” + strSDate + “’ AND tran_date <= ‘” + strEDate + “’”;

    • SQL Injection – Date Type Example
    • Expected input:
    • SELECT * FROM accounts
    • WHERE username = ‘John’
    • AND tran_date >= ‘’ OR 1=1 --’
    • AND tran_date <= ‘’ OR 1=1 --’
    • Result: Returns all saved transactions
  8. Jim Whitehead    http://www.soe.ucsc.edu/classes/cmps183/Spring06/lectures/SQL%20Injection%20Attacks.pdf

    Slide 8 - Jim Whitehead http://www.soe.ucsc.edu/classes/cmps183/Spring06/lectures/SQL%20Injection%20Attacks.pdf

    • Could be any SQL command; add data; delete rows, etc.
    • SQL Injection – Drop Table
  9. Mitigation: Prepared Statement

    Slide 9 - Mitigation: Prepared Statement

    • Pre-compiled parameterized SQL queries
    • A setter method sets a value to a bind variable as well as performs strong type checking and will nullify the effect of invalid characters, such as single quotes in the middle of a string.
    • setString(index, input), sets the bind variable in the SQL structure indicated by the index to input
    • String s="SELECT * FROM User WHERE userId = ? ";
    • PreparedStatement p = con.prepareStatement(s); p.setString(1, userIdVal); ResultSet rs = p.executeQuery();
  10. Other Mitigations

    Slide 10 - Other Mitigations

    • Hibernate framework
    • Input filtering (blacklist/whitelist)
    • All mitigation methods have holes or can be misused.
    • Defense in depth!