PDO: Escaping user input. Can I avoid replaceable parameters

I’ve not long converted from ‘mysqli’ to PDO and I’m struggling with escaping user input.
It appears that in order to use ‘prepare’ and ‘execute’ I HAVE to use replaceable parameters (:param) and/or explicitly bind each parameter. Is that correct?

My app has a user completed form with fifteen inputs, some of which can be left blank. I run a list of all the fields through a foreach loop and extract only those which are non-empty into two arrays, $inputs (field names) and $values (values). I then have a MySQL query:

$query = "INSERT INTO tablename (" . implode(', ', $inputs) . ")
				VALUES (" . implode(', ', $values) . ")";

which I can run successfully (although I suspect the ‘prepare’ is not achieving anything):

$stmt = $link->prepare($query)
$result = $stmt->execute();

BUT the inputs are not escaped. In addition to the security implications the query also fails if there’s an apostrophe in one of the inputs (e.g. last_name is O’Brien).
So I have tried creating a $params array, which is a list of field names precede by a colon. I’ve also amended the $values array so it’s associative with the parameter as the name, (e.g. :param => value) The query then becomes:

$query1 = "INSERT INTO members (" . implode(', ', $inputs) . ")
				VALUES (" . implode(', ', $params) . ")";

Now I have to bind all the params to the corresponding values:

foreach ($values as $param => $value) {
	$stmt->bindParam($param, $value);
//	echo 'bindParam(' . $param . ', ' . $value . ')<br />';
}

echoes as: bindParam(:postcode, 'PA66 6BP') but I've also tried WITH quotes as:
bindParam(':postcode', 'PA66 6BP')

Now when I run the execute function I get an error message:
Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in (the form)

I’ve carefully checked the length of all the arrays ($inputs, $values, $params) in my test form (all have 9), and I’ve searched to see if some have quotes where they should not (or vice versa). I still suspect that the problem may lie there, but it’s possible there’s some other error in my code.

It all seems a lot of work just to escape some inputs! I miss ‘mysql_real_eacape’ ! Can anyone help here, please?

Could you show the content of $values, in case there is an issue with the naming? I can’t see anything obvious wrong with what you are doing above.

There are a number of ways to avoid the rather tedious parameter binding. One way is to install the Doctrine Database Abstraction Layer (http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/) . The DBAL is a thin PDO wrapper which would reduce your insert to:

$conn->insert('table_name',$params);

All the prepared statement stuff is taken care of. Lots of other functionality as well.

Thank you for your response, droopsnoot. The current content of $values is:
Array ( [':title'] => 'Mr' [':first_name'] => 'Frederick' [':last_name'] => 'Dawson' [':addr1'] => 'Maolbhuidhe' [':town'] => 'Fionnphort' [':postcode'] => 'PA66 6BP' [':email'] => 'tim@ramasaig.com' [':tel'] => '01681700718' [':mem_status'] => 'applicant' )
(obtained from ‘print_r($values’). Here the params are quoted, and it gives rise to the error ‘Invalid parameter number…’ as in my original post.

If I remove the quotes from the params, $values becomes:
Array ( [:title] => 'Mr' [:first_name] => 'Frederick' [:last_name] => 'Dawson' [:addr1] => 'Maolbhuidhe' [:town] => 'Fionnphort' [:postcode] => 'PA66 6BP' [:email] => 'tim@ramasaig.com' [:tel] => '01681700718' [:mem_status] => 'applicant' )
and there’s no error message but the query fails silently. Unfortunately there’s no query I can paste into phpMyAdmin to see what that error is. I’ve read that there’s a way to do this, but I’ve not worked out how yet.

Thanks, ahundiak, for your reply. I’ve not heard of DBAL. I will investigate. Really PDO should be able to do this itself, and not need an third-party (?) add-on.

I have fought my way through to the MySQL log (a saga in itself) and find that the query finally being executed is:

INSERT INTO members (title, first_name, last_name, addr1, town, postcode, email, tel, mem_status)
				VALUES ('\'applicant\'', '\'applicant\'', '\'applicant\'', '\'applicant\'', '\'applicant\'', '\'applicant\'', '\'applicant\'', '\'applicant\'', '\'applicant\'')

So little wonder that it fails (The given reason is “Data too long for column ‘title’”, but obviously there’s more to it). Now I need to try to work out why the binding isn’t working as expected…

Later: It turns out I need to ‘bindValue’, NOT ‘bindParam’ in the ‘foreach’ loop (see original post), Having made that change I was able to save the data, but it was all inside single quotes. That was because when assembling my $values array I had put the value in quotes (as part of my attempt to get the script working). Now I can leave out those quotes (one would not normally use quotes here when assembling an array), and it works OK (needs more testing to be 100% sure).

The really big lesson here is that it’s possible to see the final query, with escapes and substituted parameters, in the MySQL log file. That quote can be tested in phpMyAdmin, and the problems are revealed.

1 Like

Like most open source software I’m sure the PDO maintainers will accept pull requests.

By the way, DBAL also has very simple logging capability.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.