4 Best ways to Prevent SQL Injection Vulnerability [PHP]

SQL Injection is one of the many web attack mechanisms used by hackers to steal data from organizations. It is perhaps one of the most common application layer attack techniques used today.

It is the type of attack that takes advantage of improper coding of your web applications that allows hacker to inject SQL commands into say a login form to allow them to gain access to the data held within your database. In essence, SQL Injection arises because the fields available for user input allow SQL statements to pass through and query the database directly.

Firewalls and similar intrusion detection mechanisms provide little or no defense against full-scale SQL Injection web attacks. Since your website needs to be public, security mechanisms will allow public web traffic to communicate with your web application/s (generally over port 80/443). The web application has open access to the database in order to return (update) the requested (changed) information.

Also Read: Live SQL Injection Exploitation with SQLMAP

In SQL Injection, the hacker uses SQL queries and creativity to get to the database of sensitive corporate data through the web application.

Below is the sample login code which is vulnerable to SQL Injection:

<html>
<head>
<title>Login</title>
</head>
<body>
<?php
if(!isset($_POST[‘submit’]))
{
$msg = “”;
form();
}
else
{
$username = $_POST[‘username’];
$password = $_POST[‘password’];

$conn = mysqli_connect(“localhost”,”root”,””,”yeahhub”) or die(“Error”. mysqli_error($conn));
$query = “SELECT user,pass FROM login WHERE username=’$username’ AND password=’$password'”;
$result = mysqli_query($conn,$query) or die (mysqli_error());
$count = mysqli_num_rows($result);

if($count == 1)
{
$msg = “Valid login credentials!”;
form();
}
else
{
$msg = “Invalid login credentials!”;
form();
}

mysqli_close($conn);
}
?>
</body>
</html>
<?php
function form()
{
$msg = “”;
echo ”
<form method=’POST’ name=’login’>

<table>
<tr><td>Username</td><td><input type=’text’ name=’username’ /></td></tr>
<tr><td>Password</td><td><input type=’password’ name=’password’ /></td></tr>
<tr><td colspan=’2′ align=’center’><input type=’submit’ name=’submit’ value=’Login’/></td></tr>
</table>

</form>
“.$GLOBALS[‘msg’];
}
?>

The above code does not have any filters. It will directly take the input from the form and validate it against the database which simply leads to SQL Injection vulnerability.

Below are the best four prevention methods through which you can easily prevent your web application form these types of injection based attacks:

1. Prepared Statements

The most easiest way to prevent SQL Injection Attacks in PHP is to use “Prepared Statements“. So, here’s how we can use the prepared statements for making the above database query.

Here’s how you can use prepared statements.

<?php
$username = $_POST[“username”];
$password = $_POST[“password”];
$stmt = $mysqli->prepare(“SELECT FROM login WHERE username=? AND password=?”);
$stmt->mysqli_bind_param(“ss”,$username,$password);
$stmt->execute();
$stmt->close();
$mysqli->close();
?>

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database.

So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn’t intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course).

If you are using WordPress, you can also use the wpdb::prepare statement which just like prepared statements, will sanitize the query which is sent to the database. This of course is especially important if you are using the query with user input, which is more likely to have input intended to perform SQL injection attacks. Using such functions, will also help avoid hacked WordPress sites through incorrectly programmed queries in your plugin or template.

2. Escaping Strings

Escaping string helps in removing special characters for use in SQL statements. It also takes into account current charset of the connection.

For example, you can use a code like this :

<?php
$username = mysqli_real_escape_string($conn,$_POST[“username”]);
$password = mysqli_real_escape_string($conn,$_POST[“password”]);
mysqli_close($conn);
?>

3. Using trim() and strip_tags()

trim() and strip_tags() are the conventional ways to filtering your input. trim() is used for removing whitespaces from the beginning and end of a string. strip_tags() is used for stripping HTML and PHP tags.

Both of them together can help in removing additional codes and spaces generally used by hackers.

Here’s how you can have a code like this.

<?php
$username = strip_tags(trim($_POST[“username”]));
$password = strip_tags(trim($_POST[“password”]))
?>

4. Using PDO

PDO or PHP Data Objects are very useful – probably the most effective in preventing SQL Injection Attacks. PDO also uses prepared statements and binds values at runtime. You can check out this link here for more tutorial on PDO.

For PDO, you can have a code like this :

$stmt = db::con()->prepare(“SELECT * FROM table WHERE id=? AND name=?”);
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

For binding multiple parameters, you can use named placeholders instead of ‘?’. It’s represented by :column_name.

Here’s how such a code would look like :

$stmt = db::con()->prepare(“SELECT * FROM table WHERE id=:id AND name=:name”);
$stmt->bindValue(‘:id’, $id, PDO::PARAM_INT);
$stmt->bindValue(‘:name’, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Impact of SQL Injection –

Once an attacker realises that a system is vulnerable to SQL Injection, he is able to inject SQL Query / Commands through an input form field. This is equivalent to handing the attacker your database and allowing him to execute any SQL command including DROP TABLE to the database!

An attacker may execute arbitrary SQL statements on the vulnerable system. This may compromise the integrity of your database and/or expose sensitive information. Depending on the back-end database in use, SQL injection vulnerabilities lead to varying levels of data/system access for the attacker. It may be possible to manipulate existing queries, to UNION (used to select related information from two tables) arbitrary data, use subselects, or append additional queries.

Also Read: Joomla Live SQL injection Exploitation

In some cases, it may be possible to read in or write out to files, or to execute shell commands on the underlying operating system. Certain SQL Servers such as Microsoft SQL Server contain stored and extended procedures (database server functions). If an attacker can obtain access to these procedures, it could spell disaster.

Unfortunately the impact of SQL Injection is only uncovered when the theft is discovered. Data is being unwittingly stolen through various hack attacks all the time. The more expert of hackers rarely get caught.

You may also like:

1. Prepared Statements

2. Escaping Strings

3. Using trim() and strip_tags()

4. Using PDO

Impact of SQL Injection –

Related Posts