SQL stands for Structured Query Language. To understand what SQL is, it is important to understand how information on the web is stored. A database is used to store data such that it is easy to access and manage data efficiently. There are two kinds of databases – Relational and Non-Relational. Relational database stores data in the form of rows and columns while non-relational database store data mainly in four fashions – Key-value, Graph, Column, and Document.
SQL is a standard language used to program and manage data stored in relational databases. With SQL, you can create database and tables, delete, fetch and modify rows and so on. An example of SQL database query is as follows.
SELECT Name, Description FROM Product WHERE ProductID = 100
This will select the data from the Name and Description column of the Product table where the ProductID column contains the entry 100 provided it is present.
Some of statements used for data manipulation are SELECT, INSERT, DELETE and UPDATE and does what the name suggests.
What is SQL Injection and why is it dangerous?
SQL Injection refers to the injection of malicious SQL statements in order to manipulate the backend database hence gaining access to sensitive information that was not meant to be displayed like user information or passwords.
A successful SQL attack can be devastating as the attacker may gain access to sensitive and unauthorized company data, user information and other data that is not meant for the public. Moreover, the perpetrator may delete rows and may even delete the entire table and in worst cases he may gain administrative rights to a database which is catastrophic for a business.
How does SQL Injection work?
A website may contain forms with input fields that may be used to authenticate users or add and update data in the platform. For a successful SQL attack, the perpetrator must first spot the vulnerability of the website. A website is vulnerable to such attacks if it allows users to enter special characters such as “(double quotation) which when processed in the backend is translated as a valid SQL statement.
The perpetrator could submit a malicious code that can lead to situations such as all the tables being dropped from the database, that is, deleting all the data which is disastrous for a business. For instance, if an attacker wants to bypass the authentication process in a website, he sets the password field to something like password’ OR 1=1.
Such a query will always be true granting the perpetrator access. Such authentication bypass may log the attacker in as the administrative user.
How to prevent SQL Injection?
Being a website developer, it is your duty to make sure the data stored is secured especially while storing user passwords and other sensitive data.
The first and most obvious solution is to sanitise the input data off characters such as “(double quotation). You can do so by using the HTML pattern attribute in the input tag.
Another method is to escape the characters. If you are using PHP, then use the function mysqli_real_escape_string() to escape characters in a string. The syntax is
However, the above methods are cumbersome as it must be repeated for all input fields in the website and it is likely that you forget to add in some places.
The most efficient solution is the use of Prepared Statements. Here, certain values are left specified and labeled as “?” . For example,
INSERT INTO Products VALUES(?, ?, ?)
Here the parameter values need not be escaped as the database parses, compiles, and performs query optimization on the SQL statement template. Later the application binds values to the parameters which is executed.