How to prevent SQL injection in my PHP code?

169

I developed a page in PHP for internal use of the company I work with and only very few people use it. You can use this page to do some queries, insertions, changes and deletions of a table in a MySQL database, however I believe that my PHP code is not protected against SQL injection, for example:

//----CONSULTA SQL----//
$busca = mysql_query ('insert into Produtos (coluna) values(' . $valor . ')');

So let's say the user uses the statement: 1); DROP TABLE Produtos; to the valor field the command would look like:

insert into Produtos (coluna) values(1); DROP TABLE Produtos;

It will insert a new record whose field coluna will be 1 and soon it will delete the Products table.

How can I improve my code to prevent this situation?

    
asked by Math 03.02.2014 в 13:37

9 answers

170

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 .

    
03.02.2014 / 13:46
43

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

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

    
03.02.2014 в 13:43
33

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.

    
03.02.2014 в 22:00
31

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;)

    
26.04.2014 в 18:29
28

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.

    
26.05.2014 в 18:42
19

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.

    
14.08.2015 в 22:15
7

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.

    
03.01.2016 в 05:01
5

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

filter_var('marcelo@g.c', FILTER_VALIDATE_EMAIL);//retorna boolean true
filter_var('marcelo@g.c\', FILTER_SANITIZE_EMAIL);//retorna string "marcelo@g.c"

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.

    
03.02.2018 в 05:45
2

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();
    
27.02.2018 в 16:22