[TYPO3-core] RFC #15457: Add support for prepared queries

[Jigal van Hemert]

Hi,

On 17-8-2010 13:10, Ernesto Baschny [cron IT] wrote:
> Jigal van Hemert schrieb am 17.08.2010 11:53:
>> On 16-8-2010 22:21, Xavier Perseguers wrote:
>>> - We want to provide a PDO-like syntax but we don't aim at having
>>> a PDO-compatible implementation.
>> We could build our own prepared-query-interface but this will be a
>> PHP construction which will always be slower than PDO. To take
>> advantage of the features and the speed of PDO it needs to be as
>> compatible as possible.
> True. But I guess our implementation is very in line with the PDO
> feature set already.

It comes close :-)
The reason I emphasize compatibility so much is that we run the risk of
creating an implementation which can later on not use PDO itself because
of small incompatibilities we have to support for the next few years.

E.g. it seems that with PDO you can't mix named and positioned markers;
a comment on [1] reports that the exception "SQLSTATE[HY093]: Invalid
parameter number: mixed named and positional parameters" will be the result.
If we allow this mix and people start using it things will break the
moment we replace the simulated code with actual PDO calls.

>> For example, the PARAM_AUTO feature tries to guess the value type.
(...)
>> string type column. The PDO default PARAM_STR would store it
>> correctly, whether it's stored in a numerical or a string column in
>> the database. I'm not sure about how other databases handle this.
>
> In PDO no data_type means casting to a string. If you have a WHERE
> clause with "uid='1'" (comparing a integer with a string) it will
> probably fail on more strict DB-systems, because uid is an integer.

The more reason to drop PARAM_AUTO and also drop the default PARAM_STR. 
This forces people to think about the correct datatype.

> bindValues(array(1, 'string');

This relies on guessing, (is_numeric especially), so I'd drop it.

> I expect that more extensions will be DBAL (and PDO) compatible at
> the end. After all, there *are* types in PHP, why shouldn't we rely
> on them? :)

It's slowly coming in PHP. Unfortunately there are a lot of weird 
constructions still valid in PHP.
Maybe CGL should forbid the use of implicit evaluation to boolean in 
expressions?

> We can add bindParam() later if there is the need for it or if we at

True, but that is in general true for every feature: add it if there is 
a need for it ;-P

binding values and variables to markers will help us get rid of the 
quoting for certain types of data. The security team will like this :-)

In the end using PDO can:
- increase the speed of TYPO3
- increase the speed of DBAL
- reduce the chances of SQL injections

That's what you can call a win-win-win situation ;-)

[1] http://www.php.net/manual/en/pdostatement.bindparam.php

-- 
Kind regards / met vriendelijke groet,

Jigal van Hemert
skype:jigal.van.hemert
msn: jigal at xs4all.nl
http://twitter.com/jigalvh