[RESOLVED] my PDO PREPARE isn't escaping the string

[steamPunk]

Hi

when I insert a row using the execute thing in PDO it works fine unless there's a comma in the string

here's the insert function :

function insertQuery($conn,$sql){
	$sth = $conn -> prepare($sql);
	$sth->execute();
	$insertId = $conn->lastInsertId();
	return $insertId;
}

when the $sql looks like this

INSERT INTO `page_content` ( cont_id, p_id, p_lang, p_slug, p_meta_title, p_meta_desc, p_meta_keys, p_h1_title, p_h2_title, p_content ) VALUES ('',25,'fr','nos_offres_d_emploi','','','','Nos offres d'emploi','Nos offres d'emploi','')

it fails to insert, so I guess the prepare isn't working

• how can I print the $sql AFTER the prepare($sql) to check if it's being escaped ?
• what could be preventing the prepare from working ?

thanks for any help you can give with this

[steamPunk]

hm - I 've gor the answer to the first question :

when I do

function insertQuery($conn,$sql){
	$sth = $conn -> prepare($sql);
	print "<br />SQL = ".$sth->queryString;
	$sth->execute();
	$insertId = $conn->lastInsertId();
	return $insertId;
}

it returns

SQL = INSERT INTO `page_content` ( cont_id, p_id, p_lang, p_slug, p_meta_title, p_meta_desc, p_meta_keys, p_h1_title, p_h2_title, p_content ) VALUES ('',31,'fr','test_d_emploi','','','','test d'emploi','test d'emploi','')

shouldn't the apostrophes be escaped in this string - after it has been through prepare() ?

[laserlight]

Err... you are supposed to prepare the statement and then bind values (or variables) to the placeholders. You cannot just use prepare as if it were a function for escaping values embedded in the SQL statement.

[steamPunk]

laserlight;10967795 wrote:

Err... you are supposed to prepare the statement and then bind values (or variables) to the placeholders. You cannot just use prepare as if it were a function for escaping values embedded in the SQL statement.

ah .... oh dear

shuffles off to look in the Manual

thanks anyway 🙂

[steamPunk]

comes back not completely enlightened

ok so I've altered my code so it (theoretically) uses the placeholders, but I'm getting the following error message :

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\Program Files (x86)\EasyPHP-5.3.3\includes\inc_functions.php on line 139

this is the $sql string with the placeholders :

INSERT INTO `page_content` ( cont_id, p_id, p_lang, p_slug, p_meta_title, p_meta_desc, p_meta_keys, p_h1_title, p_h2_title, p_content ) VALUES ('',?,'?','?','?','?','?','?','?','')

this is the array of vars :

$vars = array($p_id,
          $v['lang_id'],
          $urlSlug,
          $_POST['p_meta_title'],
          $_POST['p_meta_desc'],
          $_POST['p_meta_keys'],
          $_POST['p_h1_title'],
          $_POST['p_h1_title']);

and this is the way i'm using them :

function insertQuery($conn,$sql,$vars){

$sth = $conn->prepare($sql);  
print "<br />SQL = ".$sth->queryString;  
$sth->execute($vars);

$insertId = $conn->lastInsertId();
return $insertId;
}

what do you think I need to do ?

[laserlight]

I would expect the SQL statement to be written as:

$sql = "INSERT INTO `page_content`
    (cont_id, p_id, p_lang, p_slug, p_meta_title, p_meta_desc, p_meta_keys, p_h1_title, p_h2_title, p_content)
    VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?, '')";

Notice that the placeholders are not quoted. They are placeholders, not strings.

[steamPunk]

I'm still getting the same error message :

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\Program Files (x86)\EasyPHP-5.3.3\includes\inc_functions.php on line 139

could it be because a couple of the values in the $vars array are empty ?

[laserlight]

I suggest that you post the smallest and simplest script that demonstrates the problem. Don't bother with the insertQuery function for now; just get everything in the same scope to get something about the prepared statements working.

[steamPunk]

good suggestion 🙂

so I made a version of the code that simulates the data etc and it now looks like this (the correct, working version) :

       $sql = "INSERT INTO `page_content` (
        cont_id,
        p_id,
        p_lang,
        p_slug,
        p_meta_title,
        p_meta_desc,
        p_meta_keys,
        p_h1_title,
        p_h2_title,
        p_content
        ) VALUES ('',?,?,?,?,?,?,?,?,'')";

$v1 = 99;
$v2 = "fr";
$v3 = "l_ex_thing";
$v4 = "";
$v5 = "";
$v6 = "";
$v7 = "l'ex thing";
$v8 = "l'ex thing";

$vars = array($v1,$v2,$v3,$v4,$v5,$v6,$v7,$v8);

echo $sql;
foreach($vars as $v){
  echo "<br />".$v;
}
$sth = $conn->prepare($sql);  
echo "<br />SQL = ".$sth->queryString;  
$sth->execute($vars);

the problem was just exactly what the error message said : there were not enough "?" in the sql statement - only 7 instead of 8 !

But I must have counted and re-counted the '?' a million times and I would have sworn that there were 8 of them !!

now I'm going to test it in the real situation but it's probably sorted out because I added a '?' into the sql

do you know what prepare() does to the values in the array ? I can't find any documentation about this

thanks

[laserlight]

steamPunk wrote:

do you know what prepare() does to the values in the array ?

Nothing. It compiles the SQL statement into a form that can be more directly executed by the database engine.

[steamPunk]

laserlight;10967856 wrote:

Nothing. It compiles the SQL statement into a form that can be more directly executed by the database engine.

I see

Oh dear, I was under the impression it protected against SQL injection - so what would I need to do to protect against SQL attacks ??

anyway, the other code works fine now - thanks for your time 🙂

[laserlight]

steamPunk wrote:

Oh dear, I was under the impression it protected against SQL injection - so what would I need to do to protect against SQL attacks ??

It does: the values are not embedded in the prepared statement and they are treated separately when binding them to placeholders. In this way, unless the statement preparation is merely emulated, it offers a stronger protection against SQL injection than the use of some function to escape special characters.

[steamPunk]

ok - excellent - thanks for all that 🙂