WHAT IS SQL INJECTION?
It is one of the most common web hacking techniques. As the keyword, "Injection" suggests it is the insertion of harmful/poisonous code in websites and web-based applications with the aim of attacking and gathering information from our database. At times it is severe and has harmful effects on the user's personal database, and its system as well.
WHY DO WE NEED TO PREVENT IT?
The most vulnerable part of the SQL injection attack is we risk other's information also. For example, if we store information of the candidates applying for an examination or we store information of our customers. When our database is attacked their details are also at stake. So it becomes very important to protect our database from any kind of attack.
HOW DOES INJECTION/ATTACK TAKE PLACE?
- Suppose we are working on a registration process that involves filling of forms by candidates. We accept information of the candidate by using a simple form tag provided by HTML and PHP:
(all PHP codes are embedded in the basic HTML page)
- To dynamically connect and store the information to our database we use the following script:
- The attacker may use the following queries for attacking purpose:
SELECT firstname, lastame, email FROM users WHERE firstname =\"rahul\" or 1=1;This may look harmless but it is impressively destructive. The above query shows all the information whenever the attacker uses the first name as Rahul or since 1 is always equal to 1 this query is always true
SELECT * FROM Users WHERE firstname =\"\" or \"\"=\"\" AND email =\"\" or \"\"=\"\" The query above is valid and will return all rows from the \"Users\" table, since OR \"\"=\"\" is always TRUE
SELECT * FROM users WHERE firstname='shreyas'; -- ' AND email='1111' ;In the query given below the statement after ''--'' will not execute. So it would eventually mean :
SELECT * FROM users WHERE firstname='shivam';PROTECTING THE DATABASE
We can protect our database using both the White Box and Black Box approach.
WHITE BOX APPROACHES :
It is a way of testing the software in which the tester has knowledge about the internal structure of the software. It is mostly time-consuming but it is an efficient way to understand the protection algorithm. Data domains along with inner or internal boundaries can be better tested. This approach is mainly used by software developers.
1.Sanitize User Input (compatible with PHP 5 or before ONLY )- As the name suggests we sanitize/clean our input entered by the user(here candidate).
mysql_real_escape_string()- it removes all special characters in the input so that it loses its meaning when used by our database.
eg. I’m Shivam Batra, in this case, the attacker uses ‘ to inject our database, but by using mysql_real_escape_string() it is transmitted as I \\’m Shivam Batra, as a result of this whole input is passed harmlessly to our database.
This is one of the old methods to protect SQL injection but it is not up to the mark in every. It is because attackers use a set of several combinations that will by-pass the mysq_real_escape_string method.
2. Use of prepared statements (compatible with PHP 7 and further)- The main problem with select and insert statements are the mixing of code and data. Since we are dynamically accepting and storing the information received to our database the use \"=\" \"--\" in our query becomes part of the program. So keeping this in mind we use prepared statements that don't alter our program and remain intact.
Using this, we first make our database aware of the type of input it is going to receive. It is a complex internal process, we’ll ignore it but in a concise manner, we can say that in this process we ‘fill gaps’ in our input.
The detailed program and use of this statement are given in the snapshot below:
The execution is like this:
We use a prepared statement first in which data is substituted by some variable called parameter or a placeholder(?). So it is clear that the same query is sent to the server without any data.
$db->prepare(\"INSERT INTO users ($firstname,$lastname,$email) values(?,?,?)\");This function binds the variables(first name, last name, email) to the SQL query and tells the database what type of value it will receive. The \"sss\" argument states the datatype of variables. The s character tells MySQL that the parameter is a string. (we use I, d,b for integer, double, and blob values respectively)
stmt=bind_param('sss',$firstname,$lastname,$email)Then we finally send the bound data for execution.
$stmt->execute();So by using this case we minimized the risk to a great extend. Since the main problem has overcome. We are not sending the information directly at any point. This makes it very difficult for the attacker to identify the combinations.
BLACK BOX APPROACH
It is a way of software testing in which the internal structure or the program or the code is hidden and nothing is known about it. No knowledge of implementation is needed. It is a fast process but works externally. This approach is mostly used by testers.
Black box approaches would be continued in later posts.
Hope this helps!
FOR MORE INFORMATION, YOU CAN REFER TO!
https://www.php.net/manual/en/security.database.sql-injection.php
https://en.wikipedia.org/wiki/SQL_injection
CONCLUSION
We can conclude that protection is better than cure since prevention is easy and cost-effective but the curing process can prove to be very complex and expensive. It depends on the user to use either of two approaches(white box or black box) whether it is a developer or just a security checker. Black box approaches would be continued in later posts.
Comments and criticism from readers will be highly appreciated and incorporated in the further/same post.
Regards
Shivam Batra
0 Comments