OffTopic

profile picture

How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to  SQL Injection like in the following example:


$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");


That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:


INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')


What can be done to prevent this from happening?

Created at: 2018-05-24 23:22:59 by Dronning Margrethe
profile picture
Posted on: 2018-05-24 23:23:29

Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

#1
profile picture
Posted on: 2018-05-24 23:23:44
Original Posted by - Peter05:

Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

#1

Could you show me an example for it?

#2
profile picture
Posted on: 2018-05-24 23:24:16
Original Posted by - Dronning Margrethe:

Could you show me an example for it?

#2

Yes sure, wich do you prefer? PDO or mysqli?

#3
profile picture
Posted on: 2018-05-24 23:24:53
Original Posted by - Peter05:

Yes sure, wich do you prefer? PDO or mysqli?

#3

Honestly I don't know :$ sorry

#4
profile picture
Posted on: 2018-05-24 23:26:23
Original Posted by - Dronning Margrethe:

Honestly I don't know :$ sorry

#4

It's totaly fine, I show examples for both one:


  1. Using PDO (for any supported database driver):


$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
  // do something with $row
}


  1. Using MySQLi (for MySQL):


$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
  // do something with $row
}

#5
profile picture
Posted on: 2018-05-25 09:25:25

It's very helpful, thank you so much

#6

You must Log In or Sign Up to reply.

www.000webhost.com