Are these MYSQL/SQL statements safe from SQL injection?

Question

////MYSQL Statement////
$sql = $mysql_conn->prepare("UPDATE table SET columnname = ? WHERE id = ?";
$sql->execute(array($new_value,$id));


////SQL Statment////
$client_select = array($select);
$tsql1 = "SELECT * FROM customertable where id = ?";
$result1 = sqlsrv_query($conn, $tsql1,$client_select);
$row1 = sqlsrv_fetch_array($result1, SQLSRV_FETCH_ASSOC);

And if it is, can some one give me details as to why the MYSQL one is safer than:

$sql = mysql_query("select * from customers where id='$id'");
$sql = mysql_fetch_assoc($sql);

Because when you use prepared statements it automatically sanitizes your prepared variables according to their type (int/string/etc.), the `mysql` function does not. Oh, and @YourCommonSense has a very valid point. — h2ooooooo, Mar 27 '13 at 14:07
When creating a query, you have to properly format every part of it. Not because of whatever "injection" but for the sake of it. When you're going to insert a string into query, you HAVE to put it into quotes, or you will get a syntax error. When you're going to insert a string into query, you HAVE to escape these quotes were used to delimit this string, or you will get a syntax error. And so on. It is proper formatting that should be your concern, not scaring tales about injection. And as long as you have every dynamic query part properly formatted according to it's type — Daryl Gill, Mar 27 '13 at 14:09

score 2 · Answer 1 · answered Mar 27 '13 at 14:09

2

The second option

$sql = mysql_query("select * from customers where id='$id'");

is not safe because $id can be something like ' OR 1=1 OR id=' and the query look like

"select * from customers where id='' OR 1=1 OR id=''

answered Mar 27 '13 at 14:09

Voitcus

4,463
4
24
40

Ok, but why couldn't I put in: ' OR 1=1 OR id=' for the $id value in the execute portion of the prepared version? – tjberte Mar 27 '13 at 14:26
Yes, you could, it's an example. – Voitcus Mar 28 '13 at 07:23

score 0 · Answer 2 · answered Mar 27 '13 at 14:10

0

The first set are safe. The second set are not. For example, if the user were to supply data in the $id field like: '1; delete from Table1'

Then your SQL statement would turn out to be:

"select * from customers where id=1; delete from Table1"

If your SQL engine is such that it can execute multiple statements, you would have trouble.

answered Mar 27 '13 at 14:10

srini.venigalla

5,137
1
18
29

PHP’s `mysql_query` does not support multiple statements. – Gumbo Mar 27 '13 at 14:13

Jack M. · Accepted Answer · 2013-03-27T14:22:36.157

Similar to your safer query, here is an example of code I use, in a class, but you should get the idea:

$strSQL = "UPDATE user_table SET pname = ".$this->dbParam('a').", pname2 = ".$this->dbParam('b')."
            WHERE id = ".$this->pid;

$rs = parent::prepSQL($strSQL);
parent::doSQL($rs,array($a, $b));

Here is an example outside the class

$strSQL = "SELECT COUNT(*) FROM table_of_users WHERE login = ".$dbconn->Param('a')." AND guest_user = 0";
$rs = $dbconn->Prepare($strSQL);
$rs = $dbconn->Execute($rs,array($_POST["persnum_".$j]));

It is safer because it stops people from injecting their own answers; others answer that. It is also easier to read because you dont have to single quote anything.

Are these MYSQL/SQL statements safe from SQL injection?

3 Answers3