SQL Injection: How to Prevent

SQL Injection is the technique of inserting malicious code to manipulate your database. SQL Injection is the most common web hacking technique.

When a user visits website, no one knows, what’s going in his mind? Almost, every website has input fields like login/signup, search field, contact forms, other forms etc. It is not necessary that a user will input correct data. A user can input name with a special symbol or invalid email address, you have to validate until user input correct data.

If you are a developer/programmer, then you will have to keep in mind that you are not going to trust on user input. While developing you have to think like a hacker. Here are the following points you should focus on:

  • Never trust user input data
  • User HTML5 forms
  • Always filter/sanitize data
  • Escaping all invalid/unwanted/disallowed characters from input data
  • Use prepared statements ( with Parameterized Queries )
  • Use stored procedures

Escaping SQL in PHP

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.

You basically have two options to achieve this:

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
}

2. Using MySQLi (for MySQL):

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

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

PDO is the universal option. If you’re connecting to a database other than MySQL, you can refer to a driver-specific second option (e.g. pg_prepare() and pg_execute() for PostgreSQL).

Comments

Be the first to comment