Transcript SQL Injections - Andrew Mangle

SQL Injections
Prof. Andrew Mangle, PMP, CISSP, CSSLP
[email protected]
andrewmangle.com
Background
o
After reviewing the results of the qualifying round for the MidAtlantic Collegiate Cyber Defense Competition (CCDC) we
realized that our team needs to be aware of potential SQL
injection threats
o
An exmample of Security Threats reaching beyond traditional
DCOM/CSIT boundaries
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
Outcomes
•
Participants will be able to:
o
o
o
o
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
What is SQL Injection?
•
A malicious approach to use weakness in a system to extract
information using SQL (Structure Query Language)
o
o
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.
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
Bobby Tables
Why would any do this?
•
•
•
Gain unauthorized access
o Login anonymously
Sabotage an organization
o Delete/Modify/Insert their records
 Add yourself as a paid employee, change phone
numbers, etc.
Point of entry for other areas in the enterprise
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
What’s susceptible to attack?
• PHP and ASP
• Older interfaces make these susceptible
• J2Ee and ASP.Net is less susceptible
Potential Threat Vectors
Threat Vector - A path or tool an attacker uses for a target
•
•
•
Enter values incorrect values o
username: amangle or 1=1
Enter additional SQL values o
username: gotcha; drop table employees; commit;
Specialized commands*
o
';exec master..sp_makewebtask "c:\inetpub\wwroot\user.html",
"select * from users";-- [This will add a file with all the users]
Attacker Toolkit
•
•
•
•
Web Browser
Internet Connection
Web Browser Plugins
o
Remote Automation Tool
o
•
NoScript - disable JavaScript validation
o
Scan for weakness
Execute some remote action
sqlmap - automated pen testing tool
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)
Where’s the code
• IIS (~20% of market)
• C:\Inetpub\wwroot
• Apache (~50% of market)
• /usr/local/apache2/htdocs
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
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
How can a SQL Injection happen?
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>";
What made this error possible?
•
•
•
There are no input checks on the data on the server
o
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
o
o
No backups of the database
No logging of user actions
How to fix the issues?
•
In a competition, you have 15 minutes to harden the system, what
can you do?
o
Build test cases in advance


o
o
Backup the database
Check the role of the www user in the database

o
Does the server validate submissions?
Are the variables bounded?
Restrict access
Consider adding logging/journaling to the system

Monitor your logs
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
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 (ie GoDaddy – MySQL)
Defender’s Toolkit
•
•
•
•
Knowledgable staff and management
•
•
Project the database
Test the code
Updated/Patched software
Security policies and procedure
o
Secure development, testing and review
Testing Tools
o
o
o
SQL Inject Me
OWASP Tools
Burp Suite
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?
Exercise Summary
•
•
•
•
Provided an overview of SQL Injections
Presented potential attack vectors and methods
Explained a mock exploit scenario
Outlined a hardening solution
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
Questions