SQL – Injection
June 30, 2009 – 7:01 am
SQL Injection is a technique where an attacker/hacker creates or alters existing SQL queries to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build a SQL query.
Using SQL injection, a user can damage the database, delete tables, insert fake data into database, steal secure information from the database and can delete the database as well.
To avoid this issue, the code should have the ability to avoid this injection. There should be proper validations on the server end to avoid SQL injection.
The following example will make the process of SQL injection clear.
Example: Login form – when you enter text in the Username and Password fields of a login screen, the data you input is typically inserted into an SQL command. This command checks the data you’ve entered against the relevant table in the database. If your input matches table/row data, you’re granted access. If not, you’re knocked back out.
How to Test for SQL Injection Vulnerabilities:
Suppose we enter the following string in a Username field: `OR 1=1
The authorization SQL query that is run by the server, the command which must be satisfied to allow access, will be something along the lines of:
SELECT * FROM users WHERE username = `USRTEXT `AND password = `PASSTEXT`
…where USRTEXT and PASSTEXT are what the user enters in the login fields of the web form.
So entering `OR 1=1 — as your username, could result in the following actually being run:
SELECT * FROM users WHERE username = ` OR 1=1 — `AND password = `
1 is always equal to 1. So if you grant an access this means website is not secure.
Prevention Techniques: These are some of the few basic techniques that, if applied to the code, will go a long way in making the website more secure and robust.
Editing Lengths Of Form Components: There should be proper validation for field lengths. To restrict input fields to the absolute minimum- usually anywhere from 7-12 characters is fine. Doing so will make long queries unable to be input, since the field is only enough characters for smaller queries. This will actually not prevent an SQL injection, but will make the work harder for those trying to make use of one.
Data Type Validation: There should be proper data type validation implemented on all the form fields. Numeric fields should allow only the numeric values and text field should allow only the textual data. If a user had to input an age, make sure the input is an actual number. If it was a date, make sure the date is in proper format. Using this we cannot avoid sql injection, however it makes work harder for those trying to exploit an SQL server.
User Privileges: User privileges at database level should be applied properly. The main user that will be used in creating connection should not have the privileges to delete tables or database etc. It is better to be able to create a “super user” in one’s own database that can create, drop, and edit tables at will. The security-obsessive webmaster will want to make individual users that can only do one or two tasks at a time. This method is still useful for throwing attackers off track, as well as minimizing risk from areas of a website that aren’t critical to the security of the database.
Magic Quotes: Magic quotes are horrible for portability issues, performance issues, and they mess with other data that doesn’t need to be escaped
- Many scripts made with magic quotes won`t work on servers that have (intelligently) turned the feature off.
- Performance loss is observed because not all of the data is being input into a database- we are wasting process time.
- Magic quotes are just inconvenient. They add an extra slash (\) to all of our form data, even when it might not be needed. To fix this, we have to use another process to fix it (If you are unfortunate enough to have used magic quotes, look up the stripslashes() function, and consider switching if possible)