How to avoid SQL Injection in my PHP application?

Question

Explanation:

Most applications using PHP, should receive parameters, by $_GET or $_POST and these parameters often become an easy target for users with bad intentions, and this is called SQL Injection.

Question:

I would like to know how to prevent my application from being attacked via SQL Injection, how to proceed?

Answer 1

You must encode all parameters passed to your application before concatenating to your SQL. All databases support in some way saving data in the database, even if it is an SQL, and for that the value must be properly encoded.

Useful methods for encoding strings:

mysql_real_escape_string -> after using this method and escaping the string, you can concatenate into the SQL to be executed.

Or use methods that run SQL, which take parameters

There are libraries that allow indicating a single SQL, with parameter markers in the middle of SQL, and these markers are replaced in order to avoid SQL injection.

Example:

$con  = new mysqli("localhost", "u", "p", "test");
if (mysqli_connect_errno()) die(mysqli_connect_error());

$sql  = "INSERT INTO articles (title, body, date) VALUES (?, ?, NOW())";
$stmt = $con->prepare($sql);
$ok   = $stmt->bind_param("ss", $_POST[title], $_POST[body]);

if ($ok && $stmt->execute())
  header('Location: index.php');
else
  die('Error: '.$con->error);

Origin of the SOEN example

Answer 2

Personal comments

I see several people, including here at SOPT, making the mistake of leaving their application vulnerable to SQL Injection, of course due to lack of information, however it is of paramount importance to say that it is essential today to prevent this type of attack, For he’s known to a lot of ill-intentioned people.

Explanations:

"How SQL Injection works?"

SQL Injection as it says in the translated name "SQL Injection", is nothing more than an SQL script that runs in your database by someone who has no access to it, thus leaving your database totally vulnerable to anything the malicious user wants to do.

"How/Why Malicious Users Can Do This?"

The problem is in some PHP applications that use parameters that are present in the URL or even the hidden parameters that you pass in the header of your URL, to fill data in an SQL script.

Example:

You have an application that needs a ID to accomplish a SELECT, so far so good, however we will say that you have several links that send the parameter ID different:

<a href=produtos.php?id=1>Produto 1</a>
<a href=produtos.php?id=2>Produto 2</a>
<a href=produtos.php?id=3>Produto 3</a>
<a href=produtos.php?id=4>Produto 4</a>

And in the php products. you redeem the value directly and play on SELECT (what should not be done):

$id  = $_GET['id']; //aqui ele vai pegar qualquer coisa que você passar como valor do id
$sql = "SELECT * FROM TABELA WHERE ID=".$id; //aqui ele vai inserir o valor do id

Noticed the safety hazard?? who wants, can simply by deduction know that you are doing this(of which it is a very basic thing) and run more commands in your script as a DELETE for example and end your bank.

Answer: "How to solve this problem?"

Well, there are several methods to do, it depends on each programmer, but I like to always manipulate the variable before, check the correct type, and even sometimes limit the size of it, to make sure it’s not a malicious script but my information, otherwise do not execute sql, for example:

$id = $_GET['id'];
if (is_numeric($id)) //verifica se é um numero retorna true do contrario retorna false
  $sql = "SELECT * FROM TABELA WHERE ID=".$id;

This way for example, it would only run sql if $id is a number, so preventing any attempt to inject a script.

But there are other ways to perform type checks, the goal is just: Limit to the maximum the parameters you use for SQL scripts, or do not use parameters.