I'm not sure what you're doing with a single-quote table name (?) but I
observed your statements have different results after string substitution.
In the first case, substituting the "Single' Quote" string results in:
"SELECT * FROM table1 WHERE name = Single' Quote LIMIT 1"
Whereas in your "reference" case there are extra single quotes around the
argument of the name:
"SELECT * FROM table1 WHERE name = 'Single'' Quote' LIMIT 1"
Perhaps this is the reason for the difference in behavior. I know various
versions of SQL are persnickety (technical term) with respect to quoting
arguments. Your earlier statement can be changed to add single quotes to
the name:
"SELECT * FROM table1 WHERE name = '%s' LIMIT 1"%("Single' Quote",)
Which yields a single-quoted string with a single quote inside:
"SELECT * FROM table1 WHERE name = 'Single' Quote' LIMIT 1"
If that doesn't work, you can double up on the substitution argument and
get exactly the string as your "reference"
"SELECT * FROM table1 WHERE name = '%s' LIMIT 1"%("Single'' Quote",)
Which yields:
"SELECT * FROM table1 WHERE name = 'Single'' Quote' LIMIT 1"
On Tuesday, March 21, 2017 at 4:13:32 AM UTC-7, Travis Smith wrote:
>
> Hi guys,
>
> I can't seem to find anything anywhere because everything just says "it
> works".
>
> I'm using the latest version of web2py and mysql (maria) as my db, and I'm
> trying to run executesql with the following query:
>
> db.executesql("SELECT * FROM table1 WHERE name = %s LIMIT 1", ("Single'
> Quote",), as_dict=True)
>
> If I run that without the single quote, it works as expected, returning
> the value I have without the single quote. I also have another value with
> the single quote for testing.
>
> However, when I run the above, I get
>
> "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 'Quote' limit
> 1"
>
> Is this not supposed to escape everything for me and treat them as
> parameters? If not, what is the alternative? I have other, pre-existing
> tables I need to query data from, so using the db.table1 syntax won't work
> without re-creating them entirely (I think).
>
> For reference, if I run
>
> db.executesql("SELECT * FROM table1 WHERE name = 'Single'' Quote' LIMIT
> 1", as_dict=True)
>
> and manually escape the quote, everything works as expected.
>
> Any help would be appreciated.
>
>
> Thanks,
>
> Travis
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
