1 Topic by Emidio Reggiani

  • Emidio Reggiani
  • Member
  • Offline
  • Registered: 2009-08-17
  • Posts: 23

Topic: Error: (1064, "You have an error in your SQL syntax;...) in search

Hello. After updating from 1.3.0 to 1.3.1 (MySql backend) I have an error in Search feature:

Error: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%elle%' OR description LIKE u'%elle%') ORDER BY domain' at line 1")

"elle" was the search term.

I checked libs/mysql/connUtils.py, but looks fine to me (... but have to say I'm not a Python programmer).

It worked fine in 1.3.0, and I remember we had a similar issue previously that was fixed.


Thanks for your help.

Emidio Reggiani

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,137

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

- Did you choose to search Domain account?
- Does it happen again if you not choose to search Domain account?

3 Reply by Emidio Reggiani

  • Emidio Reggiani
  • Member
  • Offline
  • Registered: 2009-08-17
  • Posts: 23

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Hello Zhang. Yes, I didn't mentioned, but it was first thing I tried after getting the error. Same behavior, with slight different error:

Error: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%elle%@%' OR name LIKE u'%elle%@%') ORDER BY username' at line 1")

There's always a "u" before the second LIKE %s (OR name LIKE >>u<<'%elle%@%').

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,137

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

No idea yet, but it must be a programming bug. I will try solving it.
Could you please do me a favor: Try searching accounts with mail user only, then alias only, admin only, domain only. Let's see which account type it cannot process.

Thanks for your feedback.

5 Reply by Emidio Reggiani

  • Emidio Reggiani
  • Member
  • Offline
  • Registered: 2009-08-17
  • Posts: 23

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Searching only "user":
Error: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%elle%@%' OR name LIKE u'%elle%@%') ORDER BY username' at line 1")

Searching only "alias":
Error: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%elle%@%' OR name LIKE u'%elle%@%') AND address <> goto ORDER BY address' at line 1")

Searching only "domains":
Error: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%elle%' OR description LIKE u'%elle%') ORDER BY domain' at line 1")

Thanks for support.

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,137

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Thanks for your quick reply, will try solving it ASAP.

7 Reply by Emidio Reggiani

  • Emidio Reggiani
  • Member
  • Offline
  • Registered: 2009-08-17
  • Posts: 23

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Hello Zang. Any news?

8 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,137

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Hi Emidio, still not fixed yet, So sorry about this delay.
I'm working on porting iRedMail to Gentoo, and it's now works perfectly on Gentoo. smile

9 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,137

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Hi Emidio,

This issue was fixed. Could you please try attached patch for iRedAdmin-Pro-MySQL-1.3.1? Let me know whether it works for you or not.

Steps to apply this patch:

*) Download attached patch and upload to your server which has iRedAdmin-Pro-MySQL-1.3.1 running. Assume it's /root/search.patch.

*) Open shell terminal, change current working directory to iRedAdmin-Pro-MySQL installation directory, backup file which will be patched:

# cp libs/mysql/connUtils.py libs/mysql/connUtils.py.bak

*) Verify patch with command 'patch --dry-run':

# patch --dry-run -p0 < /root/search.patch
patching file libs/mysql/connUtils.py

*) If you got the SAME output as above sample, it's safe to apply it without '--dry-run':

# patch -p0 < /root/search.patch

*) Restarting Apache web server is recommended.

Post's attachments

search.patch 2.87 kb, 1 downloads since 2012-02-21 

You don't have the permssions to download the attachments of this post.

10 Reply by Emidio Reggiani

  • Emidio Reggiani
  • Member
  • Offline
  • Registered: 2009-08-17
  • Posts: 23

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Hello Zhang. Thanks for the patch. I made a change to make it work as it should (missing % in string WITH domain search):

Previous patch:

        sql_query_vars = {
            'search_str': searchString,
            'search_str_exclude_domain': '%%' + searchString + '%%@%%',
        }

Changed patch:

        sql_query_vars = {
            'search_str': '%%' + searchString + '%%',
            'search_str_exclude_domain': '%%' + searchString + '%%@%%',
        }

Could you please integrate this in the next release?

Thanks for your support.


Emidio

11 Reply by Emidio Reggiani

  • Emidio Reggiani
  • Member
  • Offline
  • Registered: 2009-08-17
  • Posts: 23

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

It also will be nice if the search field <input class="text" type="text" size="30" name="searchString"> could keep the search sting after submitting one (if I search for "pioli", I'd like to keep the word "pioli" in that field instead having it empty after submit).

Thanks again.

Emidio

12 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,137

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Emidio Reggiani wrote:

            'search_str': '%%' + searchString + '%%',

Sorry about my mistake, your patch is right. Thanks for your feedback. smile

13 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,137

Re: Error: (1064, "You have an error in your SQL syntax;...) in search

Emidio Reggiani wrote:

It also will be nice if the search field <input class="text" type="text" size="30" name=%26quot%3BsearchString%26quot%3B%26gt%3B could keep the search sting after submitting one (if I search for "pioli", I'd like to keep the word "pioli" in that field instead having it empty after submit).

Fixed. You can try attached patch if you want to give it a try now.

Post's attachments

search2.patch 2.14 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.