DAL executesql and escaping apostrophe/single quote

[Travis Smith]

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

[Massimo Di Pierro]

rows = db(db.table1.name=="Single ' Quote").select(limitby=(0,1))

web2py exists so that you do not have to use raw SQL. it is dangerous.

[Anthony]

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

What do you mean by "re-creating them entirely?" You certainly don't have to re-create the actual tables in the database. Though you would have to define web2py DAL models so the DAL knows how to interact with the database tables. You can get started with this with one of the extract_*_models.py files from the /web2py/scripts folder.

Anthony

[Joe Barnhart]

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"