Escaping confusion with Python 3 + MySQL

[Steve D'Aprano]

On Mon, 27 Mar 2017 02:11 am, Νίκος Βέργος wrote:

> I just tried:
> 
> domain = '.'.join( host.split('.')[-2:] )
> domain = '%' + domain + '%'
> 
> cur.execute('''UPDATE visitors SET (pagesID, host, ref, location, useros,
> browser, visits) VALUES (%s, %s, %s, %s, %s, %s, %s) WHERE host LIKE "%s"
> ''', (pID, domain, ref, location, useros, browser, lastvisit, domain) )
> 
> and i received no error in the error_log but
> ProgrammingError(1064, "You have an error in your SQL syntax; check the
> manual that corresponds to your MariaDB server version for the right
> syntax to use near '(pagesID, host, ref, location, useros, browser,
> visits) VALUES (1, '%cyta.gr%', ' at line 1")

Start by following the instructions given:

    check the manual that corresponds to your MariaDB server version 
    for the right syntax to use

just like the error message tells you to do.

Are you sure that the domain needs to have leading and trailing percentage
signs? "%cyta.gr%" instead of "cyta.gr"?

Are you sure that the LIKE clause needs double quotes?

    LIKE "%s"

Perhaps MariaDB requires single quotes:

    LIKE '%s'

or perhaps no quotes at all:

    LIKE %s


But I'm just guessing, because I haven't read the MariaDB manual. You should
do so.


> which you can see at http://superhost.gr

Ah, Nikos, its been a long time! I thought I recognised your style of
posting.




-- 
Steve
“Cheer up,” they said, “things could be worse.” So I cheered up, and sure
enough, things got worse.