SQL Injection Attacks are very common throughout the web. Hackers are known to use malicious SQL queries to retrieve data directly from the database. It basically exploits a security vulnerability. They are most common in online applications where the inputs are not correctly filtered.
Consider the following login code :
<html>
<head>
<title>Demo Login</title>
</head>
<body>
<?php
if(!isset($_POST['submit']))
{
$msg = "";
form();
}
else
{
$username = $_POST['username'];
$password = $_POST['password'];
$conn = mysqli_connect("localhost","root","","javademo") or die("Error". mysqli_error($conn));
$query = "SELECT user,pass FROM login WHERE user='$username' AND pass='$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='Submit'/></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.
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. Another effective way is to use PDO which I have discussed later.
Here’s how you can use prepared statements.
<?php
$username = $_POST["username"];
$password = $_POST["password"];
$stmt = $mysqli->prepare("SELECT FROM login WHERE user=? AND pass=?");
$stmt->mysqli_bind_param("ss",$username,$password);
$stmt->execute();
$stmt->close();
$mysqli->close();
?>
Here’s a description from Stackoverflow which explains how Prepared Statements works :
What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :username (in case of PDO) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.
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);
Here are the fetch modes of the PDO (similar to mysqli_fetch_*):
- PDO::FETCH_ASSOC : This tells PDO to return the rows as an associative array with the field names as keys
- PDO::FETCH_NUM : returns the row as a numerical array
- PDO::FETCH_BOTH : This is default behaviour and returns an array, indexed by both column-name and numeric-indexed. eg. $row[‘name’] and $row[‘1’]
- PDO::FETCH_BOUND : Returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound.
- PDO::FETCH_CLASS : Returns a new instance of the specified class.
- PDO::FETCH_OBJ : Returns an object, with property names that correspond to the columns. eg. $row->name
PDO and MySQLi are popularly known as parametrized query libraries. They work best for thwarting SQL Injection Attacks.
