Home
/
Blog
/
How to Prevent, Solve and Test SQL Injection in PHP?

How to Prevent, Solve and Test SQL Injection in PHP?

An SQL Injection Attack is presumably the simplest crime to prevent while being one of the smallest defended against modes of attack.

The focus of the attack is that an SQL call is connected to the back end of a form entries in the web or application front end, with the purpose of destroying the fundamental SQL Script and then operating the SQL script that was included in the form fields. This SQL injection most usually occurs when the user has dynamically generated SQL within any front-end application. The following is the short SQL injection cheat sheet.

SQL Injection Example

The given is the basic SQL injection example which is explaining the idea of this attack.

Suppose, in a PHP form there are two text fields’ username and password, accompanying a login button. The backend PHP code will be like this:

<?php
$UName=$_POST['UName'];
$upassword=$_POST['upassword'];
$Query="SELECT * FROM userdetails WHERE username='".$uname."' AND upassword='".$password"';";
?>

The above-written PHP code comprises a vulnerability. If a user inserts ‘ or ‘a’=’a ‘or’ then the variable $upassword will possess the value ‘ or ‘a’=’a ‘or’

<?php $Query="SELECT * FROM userdetails WHERE username='".$uname."' AND upassword='' or 'a'='a';"; ?>

In the above example, the command a=a is forever true. So the command is performed without testing the genuine password.

How to prevent SQL injection attacks?

Once the command enters the database, it is too delayed to defend from the SQL Injection attack. The unique method to absolutely defend any database application from an SQL Injection attack is to do so inside the application zone. Any other shield really won’t be as powerful.

Some people think that just by replacing a character within the SQL code will completely shield the database, and it might to a remarkable degree. But depending on how the SQL is written and how the dynamic SQL string is created, it apparently won’t. This section is explaining how to protect against SQL injection.

In PHP there are various methods with the help of which one can prevent an SQL injection attack.

Method 1

Now, to prevent SQL Injection attack the following method is displaying how to create a secure function. This method is one of the best SQL injection prevention techniques.

<?php
function SQLTest($my_string)
{
return str_replace(array("'",""","'",'"'),array("'","""'",""",$str));
}
?>

By using the above code, str_replace() function will supersede all characters in the string. Now, one can utilize the function as follows:

<?php
$Name=SQLTest($_POST['Name']);
$password=SQLTest($_POST['password']);
?>

Method 2

Another method for bypassing SQL injections is utilizing PHP Prepared Statements. A prepared statement is a trait in PHP which allows users to accomplish comparable SQL queries swiftly and regularly. Blind SQL injection is one of the most dangerous attacks.

By using prepared statements, SQL query is transmitted to the database with several undefined conditions called parameters expressed by ‘?’. The database then selects it and reserves the output without performing.

Eventually, the application connects values to the parameters before subsequently completing the statement. This allows the execution of the command repeatedly with a distinct set of conditions.

The following example is displaying how to use prepared statements to prevent SQL injection in PHP.

<?php
$statement=$connection->prepare(INSERT INTO EMP(ename,job,email)VALUES(?,?,?)");
$statement>bind_param("sss",$ename,$job,$email);
//setting parameters
$ename="Jeff";
$job="Analyst";
$email="jeff@abc.com";
$statement>execute();
$ename="Steven";
$job="Clerk";
$email="steven@abc.com";
$statement>execute();
?>

In the above example, the insert statement includes conditions (?,?,?). It means that user can replace integer, double, string or blob value. Now, the above code also contains bind_param.

This function primarily binds (connects) several parameters to the query and sends parameters to the database. ‘sss’ is a case that essentially notes the kind of data.

The value may be an integer, double, string, BLOB. By showing the database what sort of data to demand, the user primarily minimizes the chance of SQL injection.

To bypass SQL injections, user input should be validated for a limited assemblage of practices for syntax, model, and length. While granting executive powers of any database to special users, one should always strive to provide the limited powers to bypass any impending attacks to fine-tuned data.

If a user is granted powers for a particular application, one should always make sure that the user does not obtain the application needlessly. Eliminating unused stored procedures may also aid in the interference of SQL injects. One should always be cautious when handling stored procedures as they are readily misused.

How to test SQL Injection in PHP?

Testing SQL Injection vulnerability can be accomplished very smoothly. Seldom it is sufficient to just enter ‘ or “ sign in the tested domains. If it delivers any unforeseen or unusual message then one can consider that SQL Injection is probable for that domain.

For example, if the webform or application displays an error message like ‘Internal Server Error‘ as an output then the SQL injection attack is possible in that portion of the system. Other issues, that can suggest potential SQL injection attacks include Blank page displayed, no error or completion information, and complete information for the wicked code.

The following code is displaying the more reliable method to form a query for paging.

<?php
settype($offset, 'integer');
$myquery = "SELECT eid, ename FROM employee ORDER BY eid LIMIT 30 OFFSET $offset;";
$myquery = sprintf("SELECT eid, ename FROM employee ORDER BY eid LIMIT 30 OFFSET %d;",$offset);
?>

If the web page has a login page, it is likely that the web application utilizes a dynamic SQL. The dynamic SQL query is anticipated to render at least one row as the output. SQL Injection can be acknowledged as one of the most dangerous offenses, as it changes the database and can execute grave loss to the data and the entire system.

For sure it can have more severe outcomes than other cyber attacks, as some are also executed on the client-side. For correspondence, with this attack, one can have entrance to the complete database.

It should be noticed, that to examine against this attack, one should have pretty immeasurable knowledge of SQL programming language and one should know how databases queries are running.

Also while administering a SQL injection attack one should be more vigilant and attentive, as any mistake can be transmitted as SQL vulnerabilities. By following the above steps one can prevent SQL injection in PHP.

Discovering an SQL injection attack

A log file is a remarkably important piece of data that is given by a server. The computer systems, servers or any software application provide log information. Many people don’t know that a log file is crucial for finding various issues. A log file is a file which is responsible for recording all the events and actions that happen during the runtime of a machine or application

So why are log files so valuable? Log files give a particular analysis of the application or performance of a server. It also gives crucial data about when, how, and by whom” a server is being manipulated or used by someone. Such information can be crucial to monitor, study, and observe the administration, troubleshoot, and debug applications. Log files also allow forensic investigators to find, study, and investigate a number of events that may have begun the suspicious activity.

Example

Let’s take as an example a web server. Most generally, Apache HTTP Server will give two principal log files – access.log and the error.log. The access.log file is the file that records all the entries and requests for files. If a guest asks www.abc.com/main.php, the resulting record will be inserted in the log file.

88.558.126.37 – – [28/Jan/2020:08:54:09 +098] “GET /main.php HTTP/1.1” 202 205 “-” “Mozilla/5.0 (Windows NT 6.0

The above log reports that a guest with an IP address of 88.558.126.37 requested the main.php file on January 26th, 2020 and the request was successful. This data might not be too exciting, but what if the log file specified that a guest with IP 88.56.128.188 demanded dump_database.php file on January 26th, 2020 and the request was successful? In the deficiency of that log file, one might have never understood that someone found and operated a script that empties the database.

Investigation

Let’s imagine that a website got hacked. Let’s also consider that the site is a simple WordPress website running on an Ubuntu Server.

Proof to look for an investigation

To begin an examination, it is always necessary to recognize what proof to look for. In any successful cyberattack, evidence of an attack contains direct access to the database or the important files. It also contains access to the administration section with or without authentication, remote code execution, SQL injection.

Now in this example, the server access.log is accessible.

root@myserver:/var/logf/apache# access.log

The access.log gives a huge file that includes numerous events and reported requests. Reviewing every particular line would be impossible. It is always better to separate data that would most reasonably be of no concern. Instead of decreeing out some data, we separated access.log for WordPress-specific features.

root@myserver:~#cat /var/log/apache2/access.log | grep -E "wp-admin|wp-login|POST /"

The above command excludes and filters the access.log. After this, it displays only entries with strings including wp-admin. The wp-admin is the default management folder of WordPress, wp-login, which is a member of the login file of WordPress (wp-login.php), and ultimately, POST, which will display HTTP requests forwarded to the server utilizing the POST method.

88.52.46.59 - - [28/Jan/2020:07:42:07 +0100] "GET /wordpress/wp-admin/ HTTP/1.1" 200 12589 "http://www.abc.com/wordpress/wp-login.php" "Mozilla/5.0 (Windows NT 6.0; WOW64; rv:45.0) Gecko/20100101 Firefox/45.0"

The above result displays that the IP 88.52.46.59 entered the WordPress control successfully. Now, let’s check what else this IP address did. For this purpose, use the grep command to separate the access.log with that IP.

root@myserver:~#cat /var/log/apache2/access.log | grep 88.52.46.59

This gives the following records:

88.52.46.59 - - [28/Jan/2020:07:47:28 +0100] "GET /wordpress/wp-login.php HTTP/1.1" 300 1146 "-"
88.52.46.59 - - [28/Jan/2020:07:47:28 +0100] "POST /wordpress/wp-login.php HTTP/1.1" 369 1200 "http://www.abc.com/wordpress/wp-login.php"
88.52.46.59 - - [28/Jan/2020:07:47:28 +0100] "GET /wordpress/wp-admin/ HTTP/1.1" 210 14500 "http://www.abc.com/wordpress/wp-login.php"
88.52.46.59 - - [28/Jan/2020:07:47:28 +0100] "POST /wordpress/wp-admin/admin-ajax.php" 197 523 "http://www.example.com/wordpress/wp-admin/"
88.52.46.59 - - [28/Jan/2020:07:47:28 +0100] "GET /wordpress/wp-admin/theme-editor.php HTTP/1.1" 198 556 "http://www.abc.com/wordpress/wp-admin/"

The above data is displaying a timeline of the hacker’s activities that drove to the damage of the website. Still, there is an absent part of the problem. How did the hacker bypass the login authentication? The prevailing access.log did not include any evidence on what might have occurred. The Apache HTTP Server log revolution log like: /var/log/apache2/ directory gives four extra log files.

First, we want to separate the logs to understand if any steps were practiced by the IP 88.52.46.59. One of the logs was attacked with records including a bunch of SQL commands that show a SQL injection assault.

88.52.46.59- - [28/Jan/2020:07:47:28 +0100] "GET /wordpress/wp-content/plugins/custom_plugin/check_user.php?userid=1 AND (SELECT 4888 FROM(SELECT COUNT(*),CONCAT(0x6161687461,(SELECT (ELT(4888=4888,1))),0x61608a6861,FLOOR(RAND(0)*2))x FROM DATA_SCHEMA.CHAR_SETS GROUP BY x)a) HTTP/1.1" 300 266 "-" "Mozilla (Windows; U; Windows NT 6.1; ru; rv:1.8.3.2) Gecko/30200301 Firefox/4.0 (.NET CLR 4.4.10564)"
88.52.46.59- - [28/Jan/2020:07:47:28 +0100] "GET /wordpress/wp-content/plugins/custom_plugin/check_user.php?userid=(SELECT 6404 FROM(SELECT COUNT(*),CONCAT(0x6161687461,(SELECT (ELT(4888=4888,1))),0x61608a6861,FLOOR(RAND(0)*2))x FROM DATA_SCHEMA.CHAR_SETS GROUP BY x)a) HTTP/1.1" 300 266 "-" "Mozilla (Windows; U; Windows NT 6.1; ru; rv:1.8.3.2) Gecko/30200301 Firefox/4.0 (.NET CLR 4.4.10564)"

Conclusion

The number of data records in the access.log and the model show that the hacker used an SQL injection exploitation tool to use an SQL injection vulnerability. The logs of the assault that may seem like nonsense, however, they are SQL queries usually intended to obtain information via an SQL injection vulnerability. The exploitation tool attempts different SQL injection methods to obtain the database name, table name, and columns as a component of the inventory method.