PHP - how to prevent SQL injection?
Web development requires to guarant safety of application. Really serious problem is to prevent against SQL Injection Attack. This kind of attack is based on typing SQL sub querry in value place of main query. One way to prevent of this kind of attack is to escape some dangerous charactes from values. PHP provaids special API that helps to solve this problem. In this article simple solutions how to prevent the attack has been shown.
In this example solution how to use :role
agrument to prevent SQL Injection has been shown. With this approach all dangerous characters that are sent inside role argument are escaped as safe text and paced into SQL query.
xxxxxxxxxx
if(isset($_GET['role']))
{
$role = $_GET['role'];
$db_name = 'test';
$db_host = '127.0.0.1'; // 'localhost'
$db_username = 'root';
$db_password = 'root';
$dsn = 'mysql:dbname=' . $db_name . ';host=' . $db_host . ';charset=utf8';
$pdo = new PDO($dsn, $db_username, $db_password);
$statement = $pdo->prepare('SELECT * FROM `users` WHERE `role` = :role');
if($statement === FALSE)
die('Query preparation error!');
$parameters = array(
'role' => $role
);
echo '<pre>';
echo "[ID]\t[name]\t[role]<br />";
if($statement->execute($parameters))
{
while ($row = $statement->fetch(PDO::FETCH_OBJ))
{
//echo print_r($row, true);
echo $row->id . "\t" . $row->name . "\t" . $row->role . '<br />';
}
}
echo '</pre>';
}
Result:

In this example bind_param
function has been used with question mark inside SQL query to escape all dangerous characters.
xxxxxxxxxx
if(isset($_GET['role']))
{
$role = $_GET['role'];
$db_name = 'test';
$db_host = '127.0.0.1'; // 'localhost'
$db_username = 'root';
$db_password = 'root';
$mysqli = new mysqli($db_host, $db_username, $db_password, $db_name);
if($mysqli->connect_error)
die('Database connection error!');
if($statement = $mysqli->prepare('SELECT * FROM `users` WHERE `role` = ?'))
{
$statement->bind_param('s', $role); // string parameter
if($statement->execute())
{
$statement->bind_result($row_id, $row_name, $row_role);
echo '<pre>';
echo "[ID]\t[name]\t[role]<br />";
while ($statement->fetch())
echo $row_id . "\t" . $row_name . "\t" . $row_role . '<br />';
echo '</pre>';
}
else
echo 'Query execution error!';
$statement->close();
}
else
echo 'Query preparation error!';
$mysqli->close();
}
Result:

Note: this approach is marked as depricated since PHP 5.5.0 and removed sice PHP 7.0.0.
xxxxxxxxxx
if(isset($_GET['role']))
{
$unescaped_role = $_GET['role'];
$db_name = 'test';
$db_host = '127.0.0.1'; // 'localhost'
$db_username = 'root';
$db_password = 'root';
$connection = mysql_connect($db_host, $db_username, $db_password);
if($connection === FALSE)
die('Database connection error!');
if(mysql_select_db($db_name, $connection))
{
$escaped_role = mysql_real_escape_string($unescaped_role, $connection);
$query = 'SELECT * FROM `users` WHERE `role` = \'' . $escaped_role . '\'';
$result = mysql_query($query, $connection);
if($result === FALSE)
die('Query operation error!');
echo '<pre>';
echo "[ID]\t[name]\t[role]<br />";
while ($row = mysql_fetch_object($result))
echo $row->id . "\t" . $row->name . "\t" . $row->role . '<br />';
echo '</pre>';
}
else
echo 'Database selection error!';
mysql_close($connection);
}
Result:

create_tables.sql
file:
xxxxxxxxxx
CREATE TABLE `users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`role` VARCHAR(15) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
insert_data.sql
file:
xxxxxxxxxx
INSERT INTO `users`
(`name`, `role`)
VALUES
('John', 'admin'),
('Chris', 'moderator'),
('Kate', 'user'),
('Denis', 'moderator');
- PHP - how to make MySQL insert query with PDO?
- PHP - how to make MySQL select query with PDO?
- PHP - how to make MySQL update query with PDO?
- PHP - how to make MySQL delete query with PDO?