Categories
How to prevent SQL injections in PHP
What is SQL Injection?
SQL Injection is an attack that uses code injection through an unfiltered user input data. Its target is data-driven applications. A successful SQL injection can damage your data, put on a risk the sensitive information from your database, or even gets control over your SQL server.
A practical example of an SQL injection is a form submit with malicious data.
$email = "info@domain.com' OR 1=1"; mysql_query("SELECT * FROM users WHERE email = '$email'"); // will execute: // SELECT * FROM users WHERE email = 'info@domain.com' OR 1=1
Avoidance Techniques
To prevent SQL injections always escape user input data using prepared statements, type casting or database-specific string escape function (e.g. mysql_real_escape_string()
.
- PDO prepared statements
# using named placeholders $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (:email, :pswd)"); $stmt->bindParam(':email', $email); $stmt->bindParam(':pswd', $pswd); $stmt->execute(); # or $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (:email, :pswd)"); $stmt->execute(array( ':email' => $email, ':pswd' => $pswd )); # using positional ? placeholders $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)"); $stmt->bindParam(1, $email); $stmt->bindParam(2, $pswd); $stmt->execute(); # or $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)"); $stmt->execute(array($email, $pswd));
- MySQLi prepared statements
$stmt = $mysqli->stmt_init(); if ($stmt->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)")) { $stmt->bind_param("ss", $email, $pswd); $email = $_POST['email']; $pswd = $_POST['pswd']; $stmt->execute(); $stmt->close(); }
- Change data type
settype($id, 'integer'); $result = $mysqli->query("SELECT * FROM invoices WHERE id = $id");
- Type casting
$id = (int) $id; $result = $mysqli->query("SELECT * FROM invoices WHERE id = $id");
- Data format
$result = $mysqli->query(sprintf("SELECT * FROM invoices WHERE id = %u", $id));
- Escape special characters
$id = $mysqli->real_escape_string($id); $result = $mysqli->query("SELECT * FROM invoices WHERE id = '$id'");
- SQL Injection, by PHP.net
- SQL Injection, by OWASP
If you have questions about preventing SQL injections in PHP, leave a comment below. And do not forget to share this article. Thanks so much for reading!
Subscribe to our newsletter
Join our mailing list and stay tuned! Never miss out news about Zino UI, new releases, or even blog post.
0 Comments
Comments are closed