Squốc lộ injections are one of the most comtháng vulnerabilities found in web applications. Today, I’m going lớn explain what a Squốc lộ injection attaông xã is and take a look at an example of a simple vulnerable PHP. application accessing a SQLite or MySQL database. After that, we’ll look at several methods to prevent this attack, fixing the problem.
Bạn đang xem: How can i prevent sql injection in php?
Make sure you have the following software installed and enabled on your system:PHP PDO Extensions for SQLite (&, optionally, for MySQL as well)
Set up and start the exploitable PHPhường application
First, we are going to set up our vulnerable example application. It’s a really small self-contained PHP. website application that manages a menu of students from a SQLite database (also included in the app) accessed through the PDO PHPhường extension.
Let’s download the source code from GitHub.
git clone https://github.com/darkedeneurope.comdarkedeneurope.com/sql-injection-in-php.git sql-injection-in-phpcd sql-injection-in-phpcomposer install
After this, you can simply exexinh tươi the PHPhường. built-in server in the port 8080 (you can choose another port if you wish):
php -S localhost:8080PHP 7.2.29-1+ubuntu19.10.1+deb.sury.org+1 Development Server started Listening on http://localhost:8080Document root is /home/darkedeneurope.com/sql-injection-in-phpPress Ctrl-C to quit.
Now, visit the vulnerable tiện ích from your browser by navigating lớn http://localhost:8080.
Essentially, the application allows the user lớn search students by their first or last names, to add new students, & khổng lồ edit or delete existing ones.
The application is quite basic & designed khổng lồ easily show the existing SQL injection vulnerabilities just by using the browser. For example, it uses the HTTPhường. method GET for all transactions (although usually forms would be sent using methods POST or PUT).
Also, the database also includes some clear-text passwords. This is for the sake of clarity in this tutorial—I honestly hope you don’t ever kiến thiết a database or an ứng dụng this way.
What is a Squốc lộ Injection?
A SQL injection is a type of vulnerability that gives users access to the database associated with an application, allowing them lớn exedễ thương SQL queries.
Using this access, an attacker can retrieve information from the database in an unauthorized way (especially from those tables that aren’t typically accessible by users). Also, it is possible to insert, update, or delete records.
Exploit a Simple Squốc lộ Injection Vulnerability
So, how can we exploit our vulnerable application? A very good example is this classic XKCD comic strip:
Fundamentally: applications vulnerable to lớn Squốc lộ Injection attacks don’t properly sanitize their inputs, so an attacker can introduce new conditions and/or queries.
Before using Squốc lộ injection khổng lồ drop the students table, let’s play with it a bit.
In a vulnerable application, SQL queries are typically created by concatenating strings with the different parts of the query. The data from an unsanitized đầu vào would be one of these parts.
In Squốc lộ, string parameters are wrapped between quote symbols. Usually, they use single-quotes ", though some database servers also allow using double-quotes " khổng lồ wrap strings. These quote symbols separate string parameters in the SQL query from all the other components of the query.
We are going lớn try escaping from the SQL query field parameter—in which the application used the input—so it becomes something else.
So, if we are trying lớn escape from a string in Squốc lộ, we will need to use the same wrapping character that was used to start the string. As we said, this is typically the single-quote ", although some database engines also support the double-quote ". After this character, we need khổng lồ add contents so the SQL query is still valid. And, finally, we need to lớn mark the end of the SQL comm& (with the semicolon character ;). We also need the hệ thống to ignore the remaining characters added by the original software, so we’ll typically use the strings --, # or /*, depending on the engine running the database hệ thống. In our case (SQLite), we’ll use --.
So, let’s try searching for students including the following first name:" và birth_date .
The application will build this SQL query (with the highlighted part coming directly from the input đầu vào field):
By escaping from the place where the đầu vào string was originally intended, we get these results.
We have filtered the menu of students, only looking at the ones with a birth date from before October, 10th, 2007. Additionally, the limit that shows only 5 students per screen has been disabled. Essentially, we have taken control of the returned output.
As we can see in the code of includes/search.php (lines 25-45), the SQL query is generated without properly escaping any of the inputs.
$count_query = "SELECT COUNT(*) as num_rows from students where hidden=0 ";$query = "SELECT id, first_name, last_name, birth_date from students where hidden=0 ";$filters = "";if ( ! empty( $first_name ) || ! empty( $last_name ) ) if ( isset( $_GET<"first_name"> ) && ! empty( $_GET<"first_name"> ) ) $filters .= "AND first_name LIKE "%$_GET<"first_name">%" "; if ( isset( $_GET<"last_name"> ) && ! empty( $_GET<"last_name"> ) ) $filters .= "AND last_name LIKE "%$_GET<"last_name">%" "; $page = $_GET<"page"> ?? 1;$query .= $filters . " LIMIT 5 OFFSET " . ( $page - 1 ) * 5;$result = $pdo->query( $query );
The filters first_name và last_name are taken as they come, without taking any action to filter the introduction of quotes or other escaping characters khổng lồ prsự kiện the user from adding their own commands.
PDO, the database library that we are using lớn communicate with the SQLite database, doesn’t allow introducing multiple commands in a single database query to lớn get a result (the second query will be ignored). But we can still exploit this query to lớn get extra information about the database.
For example, looking at line 25, we can see that we only see those students with hidden=0. So, let’s try viewing hidden users. We can search for students with the following last name: " or 1=1 ;--
And, voilà: we can see the full menu of all students, including those that were previously hidden (only one, in our example database).
Types of Squốc lộ Injection
We can classify Squốc lộ Injection vulnerabilities inkhổng lồ several categories. Let’s examine them.
In-bvà Squốc lộ Injection attacks
These are the most comtháng category and the easiest to lớn exploit. In these attacks, the attacker uses the same communication channel lớn launch the SQL Injection và to collect the corresponding results. These are the same exploits we’ve been using in our app; we manipulate the inputs of the website application to exexinh tươi our own queries & retrieve sầu the results in the same website ứng dụng.
We can talk about different sub-categories for in-band SQL injection attacks:Boolean-based SQL Injection
In this subcategory, the attacker modifies a boolean condition in a SELECT, UPDATE, or DELETE clause in order to lớn retrieve extra information or lớn modify or delete rows that never would have sầu had lớn be modified or deleted in normal conditions. The previously shown attacks affecting the students table SELECT clause would fall into this subcategory.
This technique seeks to retrieve information about the structure of the database using error messages returned by the database server. By using this technique, an attacker can retrieve sầu valuable information about the database, such as if a given table exists in the database or not.
Xem thêm: Mysql Là Gì ? Ai Nên Học Mysql
For example, we could try exploiting this kind of attaông xã via the new student form at http://localhost:8080/manageStudent.php?action=insert. We can introduce a student injecting SQL into lớn the last field. First, we can try to find out if the tables ‘marks’ or ‘teachers’ exist in the database.To find out if the marks table exists, simply enter Test"); select * from marks; -- in the Birth Date field.
And we’ll get the following result:
So we know that the marks table doesn’t exist.
Let’s try again with the teachers table. We’ll now add another student with Test"); select * from teachers; -- as the birth date.
In this case, we don’t obtain any error, so we can assume that there’s a table named teachers.
Using this method, along with a lot of patience (and/or some automated scripts) we could determine the whole structure of the database, finding information about all of the tables và the existing columns.
That’s why it is crucial to never show database error information to the end user. These messages are very useful while developing or debugging the application, but they should be completely disabled or sent khổng lồ an error log once the phầm mềm reaches production.Concatenated-commands Squốc lộ Injection
Using the insert interface, we are able to lớn inject Squốc lộ code và ee any resulting error message. We can use this interface to obtain information, but we also have a powerful exploiting point that allows us to introduce any valid commvà inkhổng lồ the database just by concatenating commands.
This is exactly what the owner of this oto tried. Not sure if successfully.Union-based SQL Injection
Previously, we exploited the student tìm kiếm interface to lớn retrieve sầu hidden records, or to lớn change the way in which the information is returned by the database.
But we can exploit this same interface by using the UNION SQL command to lớn retrieve data from other existing tables in the database. We are limited khổng lồ select the same number and type of columns we already had in the original SELECT, but we always can use tricks to lớn circumvent this.
For example, as we already know that the teachers table exists, we could try checking its contents. We could search for students with last name " UNION select * from teachers ; --
This does, indeed, yield results: that table has the same number of columns as the original select:
It seems that the teachers table included a field with a clear password! This was a really successful attack. Now, any student using this hypothetical phầm mềm could impersonate a teacher and modify his/her qualifications!
Inferential SQL Injection attacks
In Inferential Squốc lộ Injection attacks, no return data is transferred through the channel used khổng lồ sover the manipulated input—including the attachồng. But an attacker is able lớn reconstruct the database structure by sending payloads và checking the website application’s response and/or behavior.
We have sầu two types of inferential attacks: boolean-based và time-based.Blind boolean-based SQL Injection Attacks
This kind of attaông xã relies on sending a SQL query that will force the application to lớn return a different result depending on if a given condition included in the query is true or false.
For example, if the database error-logging is disabled (& the error-based Squốc lộ Injection attaông chồng is no longer possible) we can use this method to scan tables and or columns in the database.
We could kiểm tra if the classes table exists by searching students with the following last name:" AND CASE WHEN (select count(*) from classes) >= 0 THEN true ELSE false END ; --
We wouldn’t obtain any result, so we could infer that the classes table does not exist.
But, if we repeat the process for the qualifications table, checking for students with the last name " AND CASE WHEN (select count(*) from qualifications) >= 0 THEN true ELSE false END ;--, the phầm mềm will return all the students from the database, so we know now that this table does exist.
Again, by using this method over & over, probably using an automated script, we’ll be able khổng lồ determine the whole structure of the database, finding information about all the tables & the existing columns.Blind Time-based Squốc lộ Injection Attacks
This kind of attaông xã tries lớn infer information about the database or the database hệ thống by examining the time the database spends answering a given query.
SQLite is not vulnerable lớn this kind of attaông chồng, as it doesn’t include any function that forces a delay in the executed query, nor does it include functions that take a lot of time to lớn be executed.
Other databases such as MySquốc lộ allow this kind of attack—thanks lớn the existence of functions lượt thích SLEEP() and BENCHMARK(). This attachồng can tell us if the database VPS running a given application is MySQL. For example, if in the MySQL-based version of the tiện ích we seek users with the following last name " & id=sleep(5) ; --, the query will sleep for 5 seconds or more, confirming that the ứng dụng is running against a MySquốc lộ VPS.
Additionally, the attacker might be interested in verifying a few assumptions. This can be easily done by integrating the time delay inside a conditional statement. For example, we could kiểm tra the running MySQL/MariaDB database version by searching for students with the following last name: " & id=if(LEFT(VERSION(),2)="10",SLEEP(2),1) ; -- .If running MariaDB 10, the query would take 2 seconds or more khổng lồ complete.
These time-based attacks could also be used khổng lồ cause a denial of service. By forcing the execution of several long queries simultaneously, an attacker could deplete the number of database listeners, making the tiện ích inaccessible for others.
Out-of-bvà SQL Injection attacks
These kinds of attacks occur when the result of the attaông xã is not related to the channel used lớn trigger it but is received by other truyền thông media instead. They are especially useful if the time-based techniques are not reliable because the time spent by the hệ thống when executing queries fluctuates too much.
They depend on features that must be enabled on the database server used by the web application; for example, the ability to make DNS or HTTP. requests lớn deliver data khổng lồ the attacker. These features are available in databases like Microsoft SQL Server (via the xp_dirtree command) or Oracle (via the UTL_HTTP.. package).
Preventing SQL Injection attacks in PHP
So, which measures should you take khổng lồ prevent SQL injection attacks?
It’s actually quite easy. First, you must sanitize your inputs. Always. No excuses. Don’t ever trust incoming data. The optimal & safest way to lớn sanitize inputs when building Squốc lộ queries is by using prepared statements.
And, as an additional note, don’t rely on client-side input đầu vào sanitation. An attacker could launch SQL Injection attacks emulating the calls from a browser, using unsanitized data.
How to lớn Use Prepared Statements
As stated previously, the best way lớn sanitize inputs when building SQL queries is by using prepared statements.