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 .."

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 .."