An SQL injection is an attack on a database server where a malicious actor enters – or injects – some nefarious input in a form, other than the expected input. The attack can be used to gain access to restricted data, wipe out databases, and examine the server info, among other things.
In this database programming tutorial, database developers and database administrators will learn a number of ways that a hacker can exploit your database. This database tutorial will also show you how you can protect your application from these vulnerabilities.
Read: SQL Best Practices
What are the Types of SQL Injections?
As mentioned earlier, SQL injection attacks depend on some input other than the expected input into a form. Simply put, the attacker defines the goal of the exploit, then passes an SQL statement in a form field, instead of the expected input, such as a name or number.
In the following section we will answer the question: What are the different types of SQL injection attacks?
SQL Injection Due to 1=1
This is an attack based on the fact that the condition 1=1 is always TRUE. This means that whenever a query runs, a true result is always returned, regardless of the condition (a WHERE clause).
This kind of exploit enables a hacker to gain unauthorized access to data. Here is an example of a normal SQL query:
SELECT * from Student WHERE age=27;
Here is an example of a malicious SQL query demonstrating a 1=1 attack:
SELECT * from Student WHERE age=27 OR 9=9;
As you can see from the above code example, if a malicious user enters 27 OR 9=9 in the form field, instead of just 27, then they will be able query the database for all student records regardless of the query condition.
Notice that the given example uses 9=9 instead of 1=1. This was intentionally chosen to show you that you do not have to only use 1=1 to make the exploit in the section.
You can use any number=number or character value on both sides of the equal sign ( e.g ‘g’=’g’ ).
SQL Injection Due to – –
The double-dash (—) character sequence is used to write comments in your SQL query. However, a hacker can take advantage of this property to remove/void certain segments of your SQL query. For example, to bypass password checks and query conditions.
Here is an example of how to perform an SQL — attack:
SELECT * FROM Users WHERE username = 'userX'--' AND password = ''
The above database query will allow userX to access the Users table, without making a password check.
Read: Best Certifications for Database Administrators
SQL Injection Due to Batched SQL Statements
Depending on the database server you are using, you may be able to query multiple (batched) statements at once.
A malicious actor can exploit this to add their own SQL statement. Take an example of a user who wants to access all student records. If this user is malicious, they could also add a query to DROP the Teacher table:
SELECT * from Student WHERE subject="Japanese"; DROP TABLE Teacher;
SQL Injection Due to UNION
The UNION command is used to query results from two tables. Note that the columns selected for each table used must have the same data type. A hacker can take advantage of this in the following manner:
SELECT fname, age FROM Student UNION SELECT fname, age FROM Teacher;
SQL Injection Based on “”=””
The “”=”” SQL injection attack is similar to the 1=1 injection. A hacker simply makes the “” OR “”=”” entry instead of an expected value to meet the query condition. Hence, the condition will always be true regardless of the WHERE clause:
SELECT * FROM Student WHERE fname= "" OR ""="";
SQL Injection to Examine Database
There are typically three steps involved in database vulnerability testing or hacking: (1) reconnaissance, (2) planning, and (3) attack. It is interesting to note that the military also uses a similar framework before making an attack.
Reconnaissance (or simply recon) refers to gathering info about your intended target. This is very important to help you in the next step in your hacking process – planning.
A database hacker is able to gather invaluable information about your database, such as the version, name, and distributor using either of the following SQL statements:
SELECT version(); STATUS;
How to Prevent SQL Injections
The database exploits in the above section shows you that it is important to protect your database against such known vulnerabilities. Take note that the responsibility is upon you, the application developer or database administrator, to provide protections for your database (and not the database vendor).
One way of protecting your database is using prepared statements. These are statements that do not directly insert the values they obtain from the user. In a prepared statement, the SQL server binds the values at execution time.
Here is a code example of how you can use prepared statements in PHP:
$stmt = $db->prepare("INSERT INTO Student(fname, lname, age) VALUES (?, ?, ?)");
In this code, the ? is used to replace the parameters. The bind_param() method is then used to bind the respective values to the given parameters.
$stmt->bind_param("ssi", $fname, $lname, $age);
After, you can now execute the statement:
$stmt->execute();
When binding the values, notice the string “ssi” arguments. This string specifies the data type of each input value. There are four possible letter use can use to represent the respective types:
- d: double
- i: integer
- s: string
- b: BLOB
Final Thoughts on How to Prevent SQL Injections
It is important for database programmers and database administrators to protect your database from SQL Injections. Remember, databases do not usually ship with this protection, so it is upon you, the developer or admin, to implement it.
Read: Best Online Courses to Learn SQL
The post How to Prevent SQL Injections appeared first on Database Journal.
