SQL Injections

1. SQL Injections Prof. Andrew Mangle, PMP, CISSP, CSSLP amangle@gmail.com andrewmangle.com

2. Background • After reviewing the results of the qualifying round for the Mid-Atlantic Collegiate Cyber Defense Competition (CCDC) we realized that our team needs to be aware of potential SQL injection threats • An exmample of Security Threats reaching beyond traditional DCOM/CSIT boundaries

3. Objectives • Explain SQL Injections • Outline attack vectors and methods • Perform a mock exploit scenario • Present a hardening solution • Discuss additional solutions for protecting against SQL injections and security attacks

4. Outcomes • Participants will be able to: • Explain approaches used for SQL Injection • Demonstrate how to perform an SQL Injection • Paraphase the steps used to test a system for SQL Injection Vulnerabilities • Discuss potential approaches to protect against harden systems against SQL Injection

5. What is SQL Injection? • A malicious approach to use weakness in a system to extract information using SQL (Structure Query Language) • Additional lines of code can also be executed if not properly captured • Open Web Application Security Project declares this as the most common and serious web application vulnerability in their top 10 list.

6. Threat Model • Allows a user to manipulate data, spoof an individual or circumvent authentication, or change roles • This is a high impact security threat (high severity) • Gain access to servers/databases/VoIP systems

7. Bobby Tables

8. Why would any do this? • Gain unauthorized access • Login anonymously • Sabotage an organization • Delete/Modify/Insert their records • Add yourself as a paid employee, change phone numbers, etc. • Point of entry for other areas in the enterprise

9. Case Examples • SQL Injection used to compromised- • PBS – changed the news page • Sony Pictures – 1 million passwords • Yahoo – user login information • Universities – student, faculty and staff records • And most importantly • As subplot of J.K Rowling’s – The Casual Vacancy

10. What’s susceptible to attack? • PHP and ASP • Older interfaces make these susceptible • J2Ee and ASP.Net is less susceptible

11. Potential Threat Vectors Threat Vector - A path or tool an attacker uses for a target • Enter values incorrect values - • username: amangle or 1=1 • Enter additional SQL values - • username: gotcha; drop table employees; commit; • Specialized commands* • ';exec master..sp_makewebtask "c:\inetpub\wwroot\user.html", "select * from users";-- [This will add a file with all the users]

12. Attacker Toolkit • Web Browser • Internet Connection • Web Browser Plugins • NoScript - disable JavaScript validation • Remote Automation Tool • Scan for weakness • Execute some remote action • sqlmap - automated pen testing tool

13. Locate Potential Vulnerabilities • A secure way to build SQL statements is to construct all queries with PreparedStatement instead of Statement and/or to use parameterized stored procedures. Parameterized stored procedures are compiled before user input is added, making it impossible for a hacker to modify the actual SQL statement. • The account used to make the database connection must have “Least privilege.” If the application only requires read access then the account must be given read access only. • Avoid disclosing error information: Weak error handling is a great way for an attacker to profile SQL injection attacks. Uncaught SQL errors normally give too much information to the user and contain things like table names and procedure names. • (source: https://www.owasp.org/index.php/Reviewing_Code_for_SQL_Injection)

14. Where’s the code • IIS (~20% of market) • C:\Inetpub\wwroot • Apache (~50% of market) • /usr/local/apache2/htdocs

15. Injectable Code • String Username = request.getParameter("USER"); • // From HTTP • request String Password = request.getParameter("PASSWORD"); • // From HTTP request • String sel = "SELECT User_id, Username FROM USERS WHERE Username = '" Username + "' AND Password = '" + Password + "'"; • SQL statements are dynamically created based on the user input – WHICH IS NOT GOOD in this case. • -The code has not validation (max length, special characters, [malicious or permitted] • Any can be executed directly on the server

16. Database Protection • Stored procedures (basically pre-built SQL statements) are also susceptible • Limit database roles to prevent unnecessary access • Why should you grant Admin rights to an anonymous web user? • Keep logs of the database • Set alerts for unusual behavior • Backup the database • Consider journaling

17. How can a SQL Injection happen?

18. Let’s look at the PHP code... $username = $_POST["username"]; $psswr = $_POST["password"]; $query = "SELECT * FROM $usertable where username = ". $username . " and password = " . $psswr; $result = mysql_query($query); if(mysql_fetch_array($result) !== false) echo "Correct Login - or at least that is what my script thinks<br>"; else echo "Bad username and password<br>";

19. What made this error possible? • There are no input checks on the data on the server • We use the data set to us from the server • We connect to the database using the administrator account (carte blanche access) • Additionally from an enterprise standpoint • No backups of the database • No logging of user actions

20. How to fix the issues? • In a competition, you have 15 minutes to harden the system, what can you do? • Build test cases in advance • Does the server validate submissions? • Are the variables bounded? • Backup the database • Check the role of the www user in the database • Restrict access • Consider adding logging/journaling to the system • Monitor your logs

21. Improved PHP Code • $user_id=$_SESSION['user_id']; • $login_string=$_SESSION['login_string']; • You would hash the password with a session variable so the password is not passed over clear text $username=$_SESSION['username'];// Get the user-agent string of the user.$user_browser=$_SERVER['HTTP_USER_AGENT']; if ($stmt=$mysqli->prepare("SELECT password FROM members WHERE id = ? LIMIT 1")){// Bind "$user_id" to parameter. $stmt->bind_param('i',$user_id); $stmt->execute();// Execute the prepared query. $stmt->store_result(); if($stmt->num_rows==1){// If the user exists get variables from result. $stmt->bind_result($password); $stmt->fetch(); $login_check=hash('sha512',$password.$user_browser); if($login_check==$login_string) YAY! Else Nay

22. Database Protections • Avoid dynamic table naming • If you do use them, do not allow the user to create them • Use least privileges • No admin access to internet (www) user • May not be possible for Database hosting (ieGoDaddy – MySQL)

23. Defender’s Toolkit • Knowledgable staff and management • Project the database • Test the code • Updated/Patched software • Security policies and procedure • Secure development, testing and review • Testing Tools • SQL Inject Me • OWASP Tools • Burp Suite

24. Reflect • Who’s responsibility is it to protect against SQL Injection? • Networking Staff • Developers • Project Managers • IT Governance • What steps would you put in place to prevent SQL Injection?

25. Exercise Summary • Provided an overview of SQL Injections • Presented potential attack vectors and methods • Explained a mock exploit scenario • Outlined a hardening solution

26. Additional Resources • https://www.udemy.com/blog/sql-injection-tutorial/ • http://www.w3schools.com/sql/sql_injection.asp • http://www.veracode.com/security/sql-injection • http://www.php.net/manual/en/security.database.sql-injection.php • http://www.techkranti.com/2010/03/sql-injection-step-by-step-tutorial.html • http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php • http://download.oracle.com/oll/tutorials/SQLInjection/index.htm • http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf

27. Questions