[TYPO3-dev] Reserved keywords on fields and table names in the database

[Martin Kutschker]

Ries van Twisk schrieb:
> Martin Kutschker wrote:
> 
>> Martin Kutschker schrieb:
>>  
>>
>>> Ries van Twisk schrieb:
>>>   
>>>> Hey All,
>>>>
>>>> I am heaving slowly more and more problems with reserved keywords on
>>>> different databases and versions.
>>>>     
>>> I've argued in vain that TYPO3 should quote SQL identifiers. The best 
>>> answer I got is that DBAL "takes care of that".
>>>   
>>
>> Interestingly I even had to argue with a DB programmer who normally 
>> works with Oracle that it is a good idea to quote identifiers.
>>
>> Masi
>>
>> PS: I have proposed a quoteIdentifier method for t3lib_db 
>> ($GLOBALS['TYPO3_DB']). This function is easy enough to use in a 
>> extension. But to make TYPO3 use it consistently in all DB functions 
>> is a big task.
>> _______________________________________________
>>  
>>
> DBAL should take care of this, however quoting is then also up to the 
> programmer in the current DBAL implementation.
> DBAL 'knows' about table names, but doesn't know about field names in 
> most situations, Isn't it?
> 
> For example :
> $sql['SELECT'] = 'uid, pid, name, matchtype, matchtype_value, levels, 
> nearby, geotarget, phrase, popuptype, content, groups, keywords';
> $sql['FROM'] = 'tx_sometable_items';
> $sql['WHERE'] = 'true';
> $sql['WHERE'] = 'popuptype='.intval($pt);
> $sql['WHERE'] .= 
> $GLOBALS['TSFE']->sys_page->enableFields('tx_sometable_items',-1,array());    
> 
> There is no quoting done there on the field names, and I also suspect 
> that it's time consuming to do it in DBAL (but possible).
> 
> what other options do we have?

IMHO the code that creates the statement should do the quoting (also for 
tables). That's why I suggested 
$GLOBALS['TYPO3_DB']->quoteIdentifier($ident, $table). If you write your 
own statements use it. All other code in the TYPO3 core (BE and FE API) 
should use this function internally.

Unfortunately many of the APIs accept strings as field lists, so we end 
up with a lot of split/join operations. But maybe we could create a new 
API. The current one is to me a bit scattered. There are functions in 
t3lib and tslib in a number of classes. Perhaps this can be unified in a 
new DB high-level class.

Masi