How to prevent SQL code injection into my PHP code?

Asked

Viewed 12,451 times

199

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?

  • 3

    There are many ways to prevent SQL Injection. I recommend the following reading: http://bobby-tables.com/

  • The mounted query I believe would be $search = mysql_query ('Insert into Products' (column) values('1); DROP TABLE Products;')', perhaps not accepted in the bank, but always do the processing of the data received by the user before interacting with the database....

9 answers

196


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.

  • 5

    To complete use the functions that clean the data received by POST or GET. Always try to validate your variables before passing them to an SQL. Use filter_input and filter_var to help you. See documentation. http://www.php.net/manual/en/function.filter-input.php

  • @Math The extension mysql_* was deprecated and shows an E_DEPRECATED when connecting the database from PHP 5.5 (but of course it is better to adopt Prepared statements regardless of the version you use)

  • PDO is not the most recommended for prepared queries, the preparation of queries is emulated on the client side and so is slower.

52

  • 3

    Prepared Statements seems the most correct form even as it does not allow the characters of the injection. The other option is still valid or it is also deprecated?

  • 4

    mysql_real_escape_string is already set as deprecated in PHP 5.5.0 and will be removed in future versions. PDO is the most correct one.

  • I watched a video class that the guy wore "mysqli_real_escape_string". There is no?

  • mysql_real_escape_string is part of the package of mysql_* in PHP. These functions are already triggering E_DEPRECATED. Soon they will become obsolete and - I hope - they will all migrate to the PDO or mysqli.

  • @Iwannaknow exists and is effective.

  • Using these "escape" functions is a temporary solution before replacing themysql_ API withmysqli_. It’s very easy to forget to slip away somewhere. I suggest editing the answer indicating that it is a stopgap solution and not recommended.

  • User @Iwannaknow spoke mysqli, and mysqli is still valid!!!

Show 2 more comments

39

35

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

inserir a descrição da imagem aqui

29

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.

  • 3

    Your example remains vulnerable to SQL Injection, as there is no treatment when passing the parameters name and age.

  • in stored Procedure vc sending the values as parameters there is no injection problem, do not know how in PHP call the storedprocedure to send as parameter... but doing this is 100% safe..

21

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.

11

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.

  • Also, do not leave any query with very open search can return data from another row or column of your DB

10

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.

6

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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.