[Dovecot] authentication with mysql - problem with table name escaping

[XhE at gmx.net]

Actually the answer is NO.

The backtick are only used for identifiers like table names, databases, etc.
The thing you were showing up, "@", is just a usual string. Of course I 
could replace it with single quotes instead of the double ones I was 
using in order to have consistent quoting, but it doesn't affect the 
problem ... :(

XhE

Luuk wrote:
> should u not do this;
> user_query = "SELECT CONCAT('/home/vmail/', domain.name, '/',
> address.local, '/.maildir/') AS home, 1101 AS uid, 1101 AS gid FROM
> user, `#user_address`, address, domain WHERE user.id =
> `#user_address`.user_id AND `#user_address`.address_id = address.id AND
> address.domain_id = domain.id AND user.name=LEFT('%u',
> LOCATE('@','%u')-1) AND CONCAT(address.local, '@', domain.name) =
> MID('%u', LOCATE('@','%u')+1) "
>
> so, also change: LOCATE("@",'%u')
> to: LOCATE('@','%u')
> (two times in yout query)
>
> Luuk
>
>   
>> Luuk you're absolutely right - I have to use the backtick. But
>> unfortunately dovecot doesn't let me do that.
>>
>> And further as an example (actually a pretty complicate one):
>>
>> user_query = SELECT CONCAT('/home/vmail/', domain.name, '/',
>> address.local, '/.maildir/') AS home, 1101 AS uid, 1101 AS gid FROM
>> user, `#user_address`, address, domain WHERE user.id =
>> `#user_address`.user_id AND `#user_address`.address_id = address.id AND
>> address.domain_id = domain.id AND user.name=LEFT('%u',
>> LOCATE("@",'%u')-1) AND CONCAT(address.local, '@', domain.name) =
>> MID('%u', LOCATE("@",'%u')+1)
>>
>> If I change the table's name to one, which doesn't need backtick
>> escaping the upper query works. Dovecot somehow doesn't get along with
>> the backtick ....
>>
>> Regards,
>> XhE
>>
>>
>> Luuk wrote:
>>     
>>> Hi,
>>>
>>> for more info see:
>>> http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
>>> where is stated: "The identifier quote character is the backtick (‘`’):"
>>>
>>> greetings,
>>> Luuk
>>>
>>>
>>>       
>>>> On 26.8.2007, at 3.41, XhE wrote:
>>>>
>>>>
>>>>         
>>>>> Found a problem with dovecot 1.0.3 and mysql authentication
>>>>>
>>>>> I got a problem with escaping of mysql table names. Usually, when a
>>>>> mysql table name has some special character (in my case there is a
>>>>> table beginning with the # character) one has to put into these
>>>>> special quotes --- ` ---; others like ' oder " are not recognized.
>>>>> But dovecot somehow doesn't allow me to do that.
>>>>>
>>>>>           
>>>> I don't know what you mean by putting into special quotes. Can you
>>>> given an example? But if you tried to use # it would be treated as a
>>>> comment, so you'd have to put the whole setting inside quotes:
>>>>
>>>> password_query = "select password, from #table where .."
>>>>
>>>>
>>>>
>>>>         
>>>
>>>       
>
>
>