Sqlite db question

[Asger Hallas-2]

Some javascript/style in this post has been disabled (why?)

Hi again,

 

I have a little question:

 

Using SQlite, when I try to do something like this:

 

$colName='test';

$sql=db->quoteInto("SELECT DISTINCT ? FROM sometable", $colName);

 

it will result in a string like this:

 

"SELECT DISTINCT 'test' FROM sometable"

 

which in sqlite will return a row with one coloumn named 'test' with the value 'test' no matter what's in the database in the coloums 'test'.

 

I don't know if it's a bug... I'll submit an issue for it too..

 

/Asger

###########################################

This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange.
For more information, connect to http://www.f-secure.com/

[Michael Patrick-3]

Asger Hallas wrote:

> Hi again,
>  
> I have a little question:
>  
> Using SQlite, when I try to do something like this:
>  
> $colName='test';
> $sql=db->quoteInto("SELECT DISTINCT ? FROM sometable", $colName);
>  
> it will result in a string like this:
>  
> "SELECT DISTINCT 'test' FROM sometable"
>  
> which in sqlite will return a row with one coloumn named 'test' with the
> value 'test' no matter what's in the database in the coloums 'test'.
>  
> I don't know if it's a bug... I'll submit an issue for it too..
>  
> /Asger

It isn't a bug.. you're using it wrong.  You have to quote WHERE clause
values, not field names so just write your query as

SELECT DISTINCT test FROM sometable

If you tack on a WHERE, then you start considering the ? and quoting.

[GavinZend]

Currently, the Zend Framework lacks consistent mechanisms facilitating
quoting of table names, column names, and building queries with
"expressions" such as "DATE_SUB(CURDATE(),INTERVAL 30 DAY) <=
:startOfThisMonth".

We intend to address these issues.

To see what forms of quoting are supported, please see:
http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.quoting

The framework simulates prepared queries in some places, and the issue
tracker lists some problems with the current implementation.
Additionally, when using real prepared queries (not simulated), certain
database-dependent restrictions apply.  For example, with MySQL, see:
http://dev.mysql.com/doc/refman/5.0/en/legal-names.html

"The markers are legal only in certain places in SQL statements. For
example, they are allowed in the VALUES() list of an INSERT statement
(to specify column values for a row), or in a comparison with a column
in a WHERE clause to specify a comparison value. However, they are not
allowed for identifiers (such as table or column names), or to specify
both operands of a binary operator such as the = equal sign."  --
http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-prepare.html


The Framework should provide a unified, consistent, logical approach
that helps web application developers column names, table names, and
"protect" parameter values without abandoning the native support for
prepared queries in the chosen DB.  Wherever possible, parameter values
should be protected using prepared queries supported by PHP's PDO, since
PDO uses the native API for each database, allowing explicit,
unambiguous transmission of individual parameter values, thus obviating
the need for quoting them.

I'm currently working to consolidate related issues in the issue
tracker, and identify key Zend_Db* issues to resolve for the 0.1.5 release.

Cheers,
Gavin

Michael wrote:

> Asger Hallas wrote:
>> Hi again,
>>  
>> I have a little question:
>>  
>> Using SQlite, when I try to do something like this:
>>  
>> $colName='test';
>> $sql=db->quoteInto("SELECT DISTINCT ? FROM sometable", $colName);
>>  
>> it will result in a string like this:
>>  
>> "SELECT DISTINCT 'test' FROM sometable"
>>  
>> which in sqlite will return a row with one coloumn named 'test' with the
>> value 'test' no matter what's in the database in the coloums 'test'.
>>  
>> I don't know if it's a bug... I'll submit an issue for it too..
>>  
>> /Asger
>
> It isn't a bug.. you're using it wrong.  You have to quote WHERE
> clause values, not field names so just write your query as
>
> SELECT DISTINCT test FROM sometable
>
> If you tack on a WHERE, then you start considering the ? and quoting.
>
>

[Marco2]

I'm currently working to consolidate related issues in the issue
tracker, and identify key Zend_Db* issues to resolve for the 0.1.5 release.

Great, once we have a list of DB issue's for 0.1.5 i will take a look and see which I have time to address. There were also a patch for unit tests for Zend_DB submitted by Rob Allen (I think) we should try and get these included and expanded to cover the other DB adapters.

Regards

Marco