How to prevent SQL code injection into my PHP code?

Question

I developed a PHP page for internal use of the company I work with and only very few people use it. Through this page it is possible to make some queries, insertions, changes and removals of data from a table in a Mysql database, however I believe that my code in PHP is not protected against SQL code injection, for example:

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

So let’s say the user uses the sentence: 1); DROP TABLE Produtos; out in the field valor the command would be:

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

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

How can I improve my code to prevent this situation?

Answer 1

1 - NAY use the mysql_* functions as they are considered obsolete (deprecated) and will soon be removed. A few more reasons not to use them.

Utilize 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) which will be replaced by values, the first is executed already the second even containing a valid sql statement will be treated as plain text.

Markings may not be replaced by names of Databases, tables/views, columns and values in the clasps ORDER BY or GROUP BY.

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

Example of Prepared staments 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

Example of Prepared staments 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();

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

Answer 2

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

• Php’s 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 for handling your database.

Read also: mysql_real_escape_string() versus Prepared Statements

Answer 3

It is no longer recommended the use of mysql library will be discontinued in the next versions.

So there are some solutions:

http://br2.php.net/mysqli

http://www.php.net/manual/en/class.pdo.php

http://medoo.in/ this is the one I use. Already have many generic sql ready! Worth checking out.

NOTE: the fastest library is mysqli, because underneath the two use mysqli.

Answer 4

Simply don’t 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 this allowed, otherwise would receive a crash.

In my framework I am using Notorm for the data layer, and it prevents many problems inherent in SQL Injection, and also uses the PDO interface ;) http://pedrosimoes79.github.io/silverbullet/

In the case of Notorm they use PDO::quote

http://www.php.net/manual/en/pdo.quote.php https://groups.google.com/forum/#! topic/notorm/3kR2o9iI5xQ

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

Answer 5

I found it strange that no one would quote

Stored Procedure.

In addition to avoiding SQL Injection, you gain performance since 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 don’t really remember 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);

http://php.net/manual/en/function.mssql-execute.php

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

That alias is a horror! Cursors can also be dangerous to SQL injector, but it is much more complex.

Answer 6

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

Perhaps the use of Frameworks Full Stacks - that already have several solutions ready for the programmer, from validations to security - can help in relation to the security of your application.

If there is no need to use a Framework Full Stack, i would choose to use a framework for database abstraction (Orms), such as the Doctrine 2.

I say this because Frameworks are usually designed too with this kind of concern: Safety.

Answer 7

In addition to stopping using deprecated libraries as already stated in response, you should check your queries to see if any of them are passing something sensitive without encryption, if any of your forms are passing methods on GET and etc.

There are several techniques for you to be able to prevent only depends on you finding the one that would be best in your case and using.

Answer 8

Can be used together with prepared statements the function 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 might be taking a look at documentation for an approach on SQL Injection, which Voce won’t find at first, just take a look at the constants that can be used.

Answer 9

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

For an overview of the Doctrine: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/getting-started.html covering installation with the commiserate 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 is simple and you are 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 insertion:

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

$newProductName = $argv[1];

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

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