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

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

SQL Injection Attacks

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 into 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 to 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("'","&quot;"'","&quot;",$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. The 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 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 which 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, 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 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 web form or application displays an error message like ‘Internal Server Error‘ as an output then SQL injection attack is possible in that portion of the system. Other issues, that can suggest potential SQL injection attack include Blank page displayed, no error or completion information, and completion 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.

 

About the Author

ByteScout Team

ByteScout Team of Writers

ByteScout has a team of professional writers specialized in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.

 

 

 

Try Free SQL Trainer - learn by doing!
Data Playround - upload CSV and sort, filter, view data using natural language.
prev
next