What is a SQL injection?
In general terms, code injection is the “exploitation of a computer bug that is caused by processing invalid data.” An attacker can use this exploit to introduce (or “inject”) code into a program and change the intended action of the program, which, as you can imagine, can be very dangerous.
SQL is a language that is used to communicate with databases. When a web page uses SQL to display data from a database, it is common to allow users to input their own search values. When you do a search on a website, it will usually run a SQL query. When you click on an ecommerce shopping refinement/filter, it will run a SQL query. When you log in with your username and password, it will run a SQL query; etc., etc. In specific terms, SQL injection is a technique where an attacker injects SQL commands into an SQL statement where it is unintended (i.e. a web page input field).
How is SQL injection performed?
Here is the most common and easiest example to use to understand the concept of SQL injection.
Let’s say you have a database that stores all of your clients’ information. Your database has a
Users table with a
Username column and a
Password column. On your web page, your client can view their profile and stored information by typing in their username and password into the two fields to log in.
A simple underlying SQL statement that is used to validate the username and password and retrieve user information would be:
SELECT ID, DoB, SSN, CreditCardNum FROM Users WHERE Username = $username AND Password = $password
$username contains whatever you input into the username field and the variable
$password contains whatever you input into the password field.
So if I type in tmai into the username field and h@$hy into the password field (note: not real) and submit it, the following SQL statement will run.
SELECT ID, DoB, SSN, CreditCardNum FROM Users WHERE Username = 'tmai' AND Password = 'h@$hy'
Username = 'tmai and
Password = 'h@shy' tests true, then the SQL statement will retrieve my ID, date of birth, social security number, and credit card number from the
Users table. This is pretty sensitive information.
Now, an attacker can inject SQL by doing the following:
Which in turn, runs the following SQL statement:
SELECT ID, DoB, SSN, CreditCardNum FROM Users WHERE Username = 'tmai' AND Password = 'randomtext' OR '1' = '1'
Notice the difference in the last line of the SQL statement? From the query above,
'1' + '1' will ALWAYS be true. That means that the expression
Password = 'randomtext OR '1' = '1' will be true. The attacker just successfully tricked the web page into showing all of the sensitive information that belongs to me without actually knowing my password.
The attacker can also inject SQL commands like
DELETE to erase specific data and
DROP to erase entire tables and even databases!
How can I prevent SQL injections?
There are several methods to safeguard your webpages from possible SQL injections. One method is to use data validations for your input forms against specific lengths, data types, or syntax. This may not be the best method, since it may be too limiting on what your users are allowed to type into these fields, especially if these restrictions include commonly used words. Another method to protect your database is to limit the webpage’s privileges to the database.
The better method, though, is to use SQL parameters. The SQL engine will check each parameter individually against its intended column instead of treating it as part of the entire SQL statement to be executed. To learn more about this, w3schools.com provides a lot of good examples on how to “parameterize” your SQL queries.
Now that you understand what SQL injection is and how it can be performed, make sure to take the necessary steps to protect your data.
- Acunetix – SQL Injection: What is it?
- w3schools.com – SQL Injection
- Veracode – SQL Injection Tutorial: Learn About Injection Attacks, Vulnerabilities and How to Prevent SQL Injections
This blog has been initially published on tonymai.github.io.