How to prevent SQL injection in my PHP code?

1 - DO do not use the mysql_ * functions because they are deprecated and will soon be removed. Some more reasons not to use them

Use prepared statements , this will reduce the vulnerability of sql injection , because the query is divided into two parts, the sql command and the markup ( ? or :nome ) that will be replaced by the values, the first one is already executed the second same containing a valid sql statement will be treated as plain text.

Tags can not be replaced by database names, tables / views, columns and values in the ORDER BY or GROUP BY class.

There are two drivers that support this, the PDO and the mysqli .

Sample prepared with PDO:

$db = new PDO('mysql:host=localhost dbname=teste', 'usuario', 'senha');

$sql = 'INSERT INTO tabela(campo1, campo2, campo3) VALUES(?, ?, ?)';

$stmt = $db->prepare($sql);

$stmt->bindValue(1, 'valor1');
$stmt->bindValue(2, 'valor2');
$stmt->bindValue(3, 'valor3');

$stmt->execute(); // executa o insert ou outra sql

Sample prepared with mysqli:

$db = new mysqli('localhost', 'usuario', 'senha', 'teste');

$sql = 'INSERT INTO tabela(campo1, campo2, campo3) VALUES(?, ?, ?)';

$stmt = $db->prepare($sql);
if(!$stmt){
    echo 'erro na consulta: '. $db->errno .' - '. $db->error;
}

$var1 = 1;
$var2 = 'foo';
$var3 = 1.99;
$stmt->bind_param('isd', $var1, $var2, $var3);
$stmt->execute();

The string isd corresponds to the data types passed that are integer, string, double, and b to blob fields as manual .

There are several ways to prevent SQLInjection. Two examples are:

• php mysql_real_escape_string function A generic form
• Prepared Statements The really efficient way

I recommend that you read the second option and adopt the PDO to manipulate your database of data.

Read also: mysql_real_escape_string () versus Prepared Statements

It is no longer recommended to use the mysql library to be discontinued in future releases.

There are some solutions to this:

link

link

link this is the one I use. Already have many generic sql ready! Worth checking out.

NOTE: The fastest library is mysqli, because underneath both mysqli use.

Simply do not use string concatenation, always use SQL parameters, or a lib that does it for you.

In your example with parameter the user would insert '1); DROP TABLE Products; ' in the column, if it allowed it, otherwise it would receive a crash.

In my framework I'm using NotORM for the data layer, and it prevents many problems inherent in SQL injection, and also uses the PDO interface;) link

In the case of NotORM use the PDO :: quote

link link

using this code in the Model:

function getHelloworldMySQL()
{
    $this->db->hello2->test->insert([
        'text' => "'Hello with a drop'); DROP TABLE Produtos;"
    ]);

    foreach ($this->db->hello2->test as $id => $hello) {
        return $hello['text'];
    }
}

we get;)

I found it odd to quote

Stored Procedure.

In addition to avoiding SQL Injection, you get performance as they are compiled.

Create PROCEDURE stp_ExemploInsert
@nome varchar(200),
@idade smallint

as

BEGIN
Set nocount on; --não traz nenhum retorno de linha extra;

Insert tabela_exemplo (nome,idade) values (@nome, @idade)

END

Dai in PHP, I do not remember well but it was

    $stmt = mssql_init('Nome_Procedure');

    mssql_bind($stmt, '@nome','Felipe Pena',  SQLVARCHAR,false,false,0);
    mssql_bind($stmt, '@idade ',25,SQLINT2);

 mssql_execute($stmt);
    mssql_free_statement($stmt);

link

NOTE: Stored Procedure is invulnerable as long as you do not do something like this!

CREATE PROCEDURE getDescription
   @vname VARCHAR(50)
AS
   EXEC('SELECT description FROM products WHERE name = '''+@vname+ '''')
RETURN

How alias this is a horror! Cursors can also be dangerous to SQL injector, however it is much more complex.

A good way to prevent SQL injections is already already using ready-made solutions for this.

Perhaps using Frameworks Full Stacks - which already has several ready-made solutions for the programmer, from validations to security - can help with the security of your application.

If there is no need for a Framework Full Stack , I would opt for using a database abstraction framework (ORM), such as Doctrine 2 .

I say this because frameworks are usually designed with this kind of concern too: security.

In addition to stopping deprecated libraries as already said in response, you should check your queries to see if any of them are passing anything unencrypted sensitive, if some form of yours is passing methods in GET and etc.

There are several techniques for you to be able to prevent yourself, it is up to you to find the best one for you and to use.

You can use the prepared statements function together with the filter_var() . But calm, filter_var has several utilities, for example:

filter_var('[email protected]', FILTER_VALIDATE_EMAIL);//retorna boolean true
filter_var('[email protected]\', FILTER_SANITIZE_EMAIL);//retorna string "[email protected]"

You may be taking a look at documentation for a SQL injection approach, which you you will not find it expensive, just look at the constants that can be used.

I use the ORM Doctrine framework for the data layer. Frameworks avoid SQL injection.

For an overview of Doctrine: link covering installation with composer and basic example of Object-Relational Mapping.

On your question, using the insertion of a product, this documentation has an example for the same case. It's simple and you stay protected.

<?php
use Doctrine\ORM\Annotation as ORM;
/**
 * @ORM\Entity @ORM\Table(name="products")
**/
class Product
{
/** @ORM\Id @ORM\Column(type="integer") @ORM\GeneratedValue **/
protected $id;

/** @ORM\Column(type="string") **/
protected $name;

// .. (other code)
}

The insert:

<?php
// create_product.php <name>
require_once "bootstrap.php";

$newProductName = $argv[1];

$product = new Product();
$product->setName($newProductName);

$entityManager->persist($product);
$entityManager->flush();