Prevenire sql injection

[aceveve]

Salutare,

De curand m-am hotarat sa nu mai fac struto-camile ( sa amestec proceduralul cu oop-ul ), sa trec total pe oop. Azi-maine ma apuc de clasa de sql si as avea nevoie de niste sfaturi pentru evitarea sql injection. Clasa va folosii PDO. Credeti ca e suficienta folosirea prepare, bindparam, execute? Daca nu, va rog sa ma ajutati cu sfaturi pentru o protectie cat mai mare.

Multumesc anticipat

[behave]

prepare e consumatoare de resurse de cele mai multe ori

Cea mai buna metoda impotriva la sql injection este sa verifici daca datele trimise sunt cele care trebuie sa fie trimise...

De exemplu, daca astepti sa primesti un integer atunci te folosesti de intval, pt altele mai complexe regex

Parerea mea ca daca faci u cms-ul tau nu trebuie sa pasesti dupa framework-uri si sa faci codul mai putin consumator de resurse... E plin net-ul de benchmark-uri... foloseste-le

[Geoutsu]

Poti filtra intrarile

<?php

// Anti-SQL Injection

function check_inject()

{

$badchars = array(";","'","*","/"," \ ","DROP", "SELECT", "UPDATE", "DELETE", "drop", "select", "update", "delete", "WHERE", "where", "-1", "-2", "-3","-4", "-5", "-6", "-7", "-8", "-9",);

foreach($_POST as $value)

{

$value = clean_variable($value);

if(in_array($value, $badchars))

{

die("SQL Injection Detected - Make sure only to use letters and numbers!\n<br />\nIP: ".$_SERVER['REMOTE_ADDR']);

}

else

{

$check = preg_split("//", $value, -1, PREG_SPLIT_OFFSET_CAPTURE);

foreach($check as $char)

{

if(in_array($char, $badchars))

{

die("SQL Injection Detected - Make sure only to use letters and numbers!\n<br />\nIP: ".$_SERVER['REMOTE_ADDR']);

}

}

}

}

}

function clean_variable($var)

{

$newvar = preg_replace('/[^a-zA-Z0-9\_\-]/', '', $var);

return $newvar;

}

?>

[behave]

Pai aplicatiile sunt facute pentru utilizatori...

Conform functiei tale eu nu pot da citate si nu imi pot sa-mi fac cont cu username update

[dancezar]

Cand PDO este folosit corect cu prepared query , este deajuns , dar daca nu il folosesti corect cam asa se intampla : https://rstforums.com/forum/90935-drupal-7-31-pre-auth-sql-injection-vulnerability.rst ...

Totusi din start tu trebuie sa ai in vedere ce tip de date iti da utilizatorul ca input poti face o functie de genu pt a escapa numere si stringuri:

<?php
    function sqli_escape($var,$type){
              if($type==0)
                    return (int)$var;
              else
                    return mysql_real_escape_string($var);
   }

echo "Numarul 123a escapat este:".sqli_escape("123a",0)."<br>";//Pentru numere
echo "Un test: ".sqli_escape("'test'",1);// Pentru stringuri.
?>

[robyyxx2]

eu folosesc asta ajuta si la xss

function xsssql($dirty){
	if (get_magic_quotes_gpc()) {
		$clean = mysql_real_escape_string(stripslashes($dirty));	 
	}else{
		$clean = mysql_real_escape_string($dirty);	
	} 
	return htmlentities($clean);
}

ex: $var=xsssql($var);

[aceveve]

Cum zicea si behave aplicatiile sunt facute pentru utilizatori, asadar chestia cu "trebuie sa ai in vedere ce tip de date iti da utilizatorul" pica din start.

Chiar daca in toata aplicatia ai un singur input deasupra caruia scrie "in casuta de mai jos scrie numarul 1 si vei castiga o mie de euro" fi sigur ca o sa se gaseasca idioti care nici macar sa nu scrie un integer acolo sau baietii destepti care testeaza vulnerabilitatea aplicatiei.

[BitMap]

Doar ca si o idee, uite cum face o platforma de magazin online sanitizarea. Am cautat din curiozitate.

// da un find dupa '(int)'

https://github.com/PrestaShop/PrestaShop/blob/1.6/classes/Category.php

// sau cand e vorba de string, foloseste functia pSQL()

https://github.com/PrestaShop/PrestaShop/blob/1.6/classes/Product.php

// care e definita aici

https://github.com/pal/prestashop/blob/master/classes/Db.php

[Dragos]

Functia mysql_real_escape_string nu este suficienta pentru a bloca SQLi.

Spre exemplu avem scriptul acesta

$id = mysql_real_escape_string($_GET['id']);
$sql = mysql_query("select * from tabel where ID=$id");

Daca introduc in GET "1 or 1=2" o sa se execute scriptul.

Nu folosi nici scriptul lui @Geoutsu pentru ca se poate trece de el printr-un string codat in hexa.

Cel mai usor foloseste htmlentities cu flag-ul ENT_QUOTES.

$id = htmlentities($_GET['id'],ENT_QUOTES);
$sql = mysql_query("select * from tabel where ID=$id");

[dancezar]

Cum zicea si behave aplicatiile sunt facute pentru utilizatori, asadar chestia cu "trebuie sa ai in vedere ce tip de date iti da utilizatorul" pica din start.

Chiar daca in toata aplicatia ai un singur input deasupra caruia scrie "in casuta de mai jos scrie numarul 1 si vei castiga o mie de euro" fi sigur ca o sa se gaseasca idioti care nici macar sa nu scrie un integer acolo sau baietii destepti care testeaza vulnerabilitatea aplicatiei.

Cum adica "pica" tu ai o aplicatie care face select dintr-un tabel dupa un ID numeric, deci clar utilizatorul trebuie sa iti dea o valoare numerica ,daca nu ti-o da numerica o faci tu ca sa fie numerica.

$var="123a";

echo (int)$var;// 123

Aici chiar daca iti da un numar sau nu ti-l converteste in INT

@Dragos uitate la functie sqli_escape($text,0) pentru numere in cazul tau , iara sqli_escape($text,1) pentru stringuri

Oricum pt lucruri simple PDO este deajuns

Edited October 27, 2014 by danyweb09

[behave]

mysql_real_escape_string este deprecated, dar are si versiune pdo care isi face treaba foarte bine..

Pentru ca scripturile custom trebuie sa aiba un + fata de framework-uri acel plus ar trebui sa fie optimizarea.

Din acel motiv tin sa subliniez din nou ca nu este bine sa dai prepare la toate query-urile: primul link din google zice asa

PHP MySQL prepared SQL statement vs SQL statement | ErlyCoder

Un alt lucru care as dori sa-l subliniez este ca string-urile trebuiesc verificate inainte de a ajunge la query pentru a putea interactiona cu utilizatorul.. majoritatea lor nu sunt h4x0ri... de aceea indiferent de camp ii poti pune escape-ul din sql iar testul il faci mai sus pentru a putea oferi feedback..

[aceveve]

Cum adica "pica" tu ai o aplicatie care face select dintr-un tabel dupa un ID numeric, deci clar utilizatorul trebuie sa iti dea o valoare numerica ,daca nu ti-o da numerica o faci tu ca sa fie numerica.

$var="123a";

echo (int)$var;// 123

Aici chiar daca iti da un numar sau nu ti-l converteste in INT

@Dragos uitate la functie sqli_escape($text,0) pentru numere in cazul tau , iara sqli_escape($text,1) pentru stringuri

Oricum pt lucruri simple PDO este deajuns

Ar fi prea frumos sa avem doar select dupa un id numeric. Dar de exemplu intr-o aplicatie mediocra avem si o cautare, unde utilizatorul poate sa caute in principiu cam orice de la oi pana la si regina cea rea a spus: "sa moara Alba ca zapada!".

[dancezar]

Ar fi prea frumos sa avem doar select dupa un id numeric. Dar de exemplu intr-o aplicatie mediocra avem si o cautare, unde utilizatorul poate sa caute in principiu cam orice de la oi pana la si regina cea rea a spus: "sa moara Alba ca zapada!".

In concluzie poti folosi PDO cu prepare mai putin eficient adevarat cum zicea behave sau cu query() folosind functia escape_string()/quote() pt a escapa stringuri , sau operatorul de conversie (int) pt numere.

De accea iti zic ca ai 2 cazuri si trebuie sa stii ce input trebuie sa folosesti , ori cuprinzi toate inputurile intre ghilimici peste tot chiar daca inputul trebuie sa fie un numar caz in care vei folosi peste tot in query-uri escape_string()/quote(), sau sa ai invedere daca trebuie sa folosesti in query un numar sa il convertesti in (int) sau daca e string sa folosesti escape_string()/quote()

Edited October 27, 2014 by danyweb09

[Ganav]

Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

Using PDO:

 $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

    $stmt->execute(array('name' => $name));

    foreach ($stmt as $row) {
        // do something with $row
    }

Using MySQLi:

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name);

    $stmt->execute();

    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // do something with $row
    }

PDO

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And it gives the developer the chance to catch any error(s) which are thrown as PDOExceptions.

What is mandatory however is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (< 5.3.6) silently ignored the charset parameter in the DSN.

Explanation

What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit with using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute(array('column' => $unsafeValue));

You've got two options - escaping the special characters in your unsafe_variable, or using a parameterized query. Both would protect you from SQL injection. The parameterized query is considered the better practice, but escaping characters in your variable will require fewer changes.

We'll do the simpler string escaping one first.

//Connect

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

//Disconnect

See also, the details of the mysql_real_escape_string function.

Warning:

As of PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. Please use mysqli extension and

mysqli::escape_string

function instead

To use the parameterized query, you need to use MySQLi rather than the MySQL functions. To rewrite your example, we would need something like the following.

<?php
    $mysqli = new mysqli("server", "username", "password", "database_name");

    // TODO - Check that connection was successful.

    $unsafe_variable = $_POST["user-input"];

    $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");

    // TODO check that $stmt creation succeeded

    // "s" means the database expects a string
    $stmt->bind_param("s", $unsafe_variable);

    $stmt->execute();

    $stmt->close();

    $mysqli->close();
?>

The key function you'll want to read up on there would be mysqli::prepare.

Also, as others have suggested, you may find it useful/easier to step up a layer of abstraction with something like PDO.

Please note that the case you asked about is a fairly simple one, and that more complex cases may require more complex approaches. In particular:

If you want to alter the structure of the SQL based on user input, parameterised queries are not going to help, and the escaping required is not covered by mysql_real_escape_string. In this kind of case you would be better off passing the user's input through a whitelist to ensure only 'safe' values are allowed through.

If you use integers from user input in a condition and take the mysql_real_escape_string approach, you will suffer from the problem described by Polynomial in the comments below. This case is trickier because integers would not be surrounded by quotes, so you could deal with by validating that the user input contains only digits.

There are likely other cases I'm not aware of. You might find Home - Web Application Security Consortium a useful resource on some of the more subtle problems you can encounter.

Sursa: mysql - How can I prevent SQL-injection in PHP? - Stack Overflow