- Category: Web Development
Not sure how many of these I've written but I decided to start this article on this site as I've been replying to other peoples web articles on SQL-Injection telling them how their prevention methods won't work. I feel that there are lots of articles on the web explaining how SQL-Injection works but not how to prevent these kind of attacks. I can't make a system unhackable but I can list a number of ways to make it much harder and also what to look for in your system to see what's vulnerable. For a quick checklist and simple tests, please read my article: Basic tests for SQL-Injection vulnerabilities.
Now I don't usually find worth in writing an article criticizing others but I'm not aiming to criticize. Hopefully by following this you can see where the vulnerabilities will be in your system. Webdevelopers have to know what this is because according to OWASP it accounts for 60% of website hacks with Tech Herald saying half a million sites were hacked in 2008 using this technique.
An entry that allows users to enter text that will be used to interact with a website database? Well depending on the permissions of the database user running the script, they can drop/alter/create tables and content, execute/. You could come in to work to find your company website rebranded (usually with the title "Hacked by ..."), defaced (referencing files on remote sites). If vulnerable, you can expect that all your customers details can be exposed (see Amusing Examples below).
If your website is database-driven then basically your website will need to be restored along with an upgrade to prevent future hacks. Factor in your IT staff spending a minimum of a day analysing 60'000 rows of logs (manually) to examine how the hacker got in and how to prevent it.
We're going to cover SQL-injection here (and not XSS-Injection because Cross-site scripting or XSS, depending on your server setup, is more client-side orientated -- arguable). Consider the following query for a website login form:
SELECT * FROM Logins WHERE Username='admin' AND Password='secret'The HTML form will be asking for your username and password. So in the example above, we entered "admin" as the username and "secret" as the password. This query will fail if the password is not "secret"... Sounds straightforward enough.
For those of you that don't know your SQL, think of it as a statement which has to evaluate to true or false. If true (so both username and password are the ones in the database), the system will let you in.
Herein lies the problem
Notice how the SQL query is written with apostrophes, well what if the user typed in their own apostrophes? This changes the query so we'll put as username "admin' OR '1'='1" (notice how I don't need a last apostrophe). Submitted to the same form as before the query becomes:
SELECT * FROM Logins WHERE Username='admin' or '1'='1' AND Password='secret'If you know your SQL, you'll admit the above is a valid query which will evaluate to TRUE. Even if the password is NOT "secret" this will always evaluate to TRUE because 1 is definitely equal to 1. So in plain english, the password can be any value if the username field is not checked for SQL-Injections.
The Challenge by Author #1: VB Helper
Find a way to circumvent my anti-SQL-injection script which replaces apostrophes with double apostrophes (escapes apostrophes as strings).
// Yields SELECT * FROM Logins WHERE Username='admin'' or ''1''=''1' AND Password='secret'
My message to the author
Escaping the apostrophes in any of the languages is not a silver bullet, and unfortunately would not affect url-encoded or account logins dependant on numbers.
Consider the following (also valid):
• Username = "admin%27%20OR%20%271%27=%271"
• Password = "whatever"
-- Yields SELECT * FROM Logins WHERE Username='admin' OR '1'='1' AND Password='whatever'Systems that expect/use a number could be the following:
• 1&1 Username = "105876572587%20OR%201=1"
• Password = "who cares?"
-- Yields SELECT * FROM Logins WHERE Username=105876572587 OR 1=1 AND Password='who cares?'
Basically any variables in any HTML form on your site, especially free text fields (where the user can type any text) as well as any dynamic variables (which always catches Microsoft out). Dynamic variables tend to be a variable that was generated on-the-fly and a tell-tale sign is usually when it is suffixed with a number (eg. item1, item2, item3...).
Ignore anyone who tells you it's only variables that can be added to the URL/web address of the site because it's a small matter to create a form on a test server which posts (using POST method) these variables to your website. And just because you're using SSL on a HTTPS domain, this doesn't prevent these kind of attacks!
- Setup at least 2 database users. One which your developers will use when dealing with the database directly. Another with just SELECT permissions, so Read-Only! (I also include USE TEMPORARY TABLES in the case of some sites driven by a CMS).
- Nearly all Microsoft SQL Server web applications by default are vulnerable. Oracle's MySQL is not!
- If you are expecting a received form variable to be a number, multiply it by 1 (just in case). Anything that is not a number should return -1 (negative 1).
- Examine website pages with more than 1 HTML form. Check the variable names are all accounted for (especially dynamic ones).
- Blind SQL-Injection: make your system database and its usernames unrelated or made of words that could not have been extrapolated from your website. Verify what standards you ought to conform to, and where in the interests of security, you deviate from the norm.
My favorite is checking the username, looking up the password in the database, then comparing that password to the one the user has posted. Your query become:
SELECT Password FROM Logins WHERE Username='admin';You can check the username posted by the user to no end because most usernames should be alphanumeric and not contain any symbols (ie. escape apostrophes, trim, check they're in the format you use). (For emails just allow an @ symbol and that's it). Now using the VB or PHP you are using, compare the retrieved value to the one the user posted.
// PHP If ($_REQUEST['Password']==mysql_result($results, 0, "Password")) then login // VBScript/Asp If (request.form("Password")==Request.ServerVariables("AUTH_PASSWORD")) then login
General Anti-SQL-Injection Method #2:
On most of my websites this is the one I use. A huge function checking for various forms of SQL-Injection which then emails all system administrators with as many details on the client that the system can find. If you can, my "secret" weapon (scuse the quotes but it's not really a secret) is to automatically block these IP addresses when they
- try to send more than 100 queries per second to the database
- Include a SQL command in the HTML form
- Stored Procedures (I personally feel these have to include variable validation otherwise these are vulnerable no matter what your webdeveloper says)
- June 29, 2007 Official Microsoft UK website gets defaced using SQL-Injection.
- April 13, 2008 10,597 social security numbers belonging to sex offenders had been downloaded via an SQL injection attack
- August 17, 2009 A US citizen (along with 2 Russian associates) are charged with the theft of 130 million credit card numbers using an SQL injection attack.
- March 27th 2011: MySQL.com, the official homepage for MySQL, was compromised using SQL blind injection.
- the Open Web Application Security Project (OWASP)
- Database management system fingerprint
- OWASP Conference: "SQL-Injection: Not Only AND 1=1"
- Anubhav Goyal: "Are stored procedures safe against SQL injection?"
- Ferruh Mavituna: SQL Injection Cheat Sheet