Escaping Strings

[Lawrence D'Oliveiro]

In standard SQL, backslashes have no special significance. String literals
are delimited by single quotes, and any characters (except single quotes)
are allowed in them; to include a single quote in the string, write it
twice.

So in Python, this expression is sufficient to turn a string “s” into a
standard SQL string literal:

"'" + s.replace("'", "''") + "'"

[J.O. Aho]

Post by Lawrence D'Oliveiro
In standard SQL, backslashes have no special significance. String literals
are delimited by single quotes, and any characters (except single quotes)
are allowed in them; to include a single quote in the string, write it
twice.
So in Python, this expression is sufficient to turn a string “s” into a
"'" + s.replace("'", "''") + "'"

I would looked into using parameterized queries, here is a python example
https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/

[Lawrence D'Oliveiro]

I would looked into using parameterized queries ...

There are lots of cases they don’t handle. Like for example LIKE and
REGEXP operands.

[J.O. Aho]

Post by Lawrence D'Oliveiro

I would looked into using parameterized queries ...

There are lots of cases they don’t handle. Like for example LIKE and
REGEXP operands.

LIKE:
select * from table where column1 like ?;

you have the %'s in the parameterized value.

If you have issues, you can always use functions or store procedures
which you use with parametrization.

[Lawrence D'Oliveiro]

Post by J.O. Aho

Post by Lawrence D'Oliveiro

I would looked into using parameterized queries ...

There are lots of cases they don’t handle. Like for example LIKE and
REGEXP operands.

select * from table where column1 like ?;

Like: you want to do a partial match on what the user typed. And what
the user typed can include characters like “%” and “_”, which you
don’t want to be mistaken for wildcards.

Another example: can your parameterized queries handle dynamic SQL
like this?

for artwork_url, timestamp in \
db_iter \
(
conn = db,
cmd =
"select artworks.artwork_url as artwork_url,"
" %(func)s(artwork_stats.timestamp) as timestamp"
" from artworks inner join artwork_stats on"
" artworks.artwork_url = artwork_stats.artwork_url"
" group by artwork_stats.artwork_url"
" order by timestamp %(order)s"
%
{
"func" : ("min", "max")[which == "latest"],
"order" : ("asc", "desc")[which == "earliest"],
}
) \
:
sys.stdout.write \
(
"%s %s\n"
%
(artwork_url, format_timestamp(timestamp))
)
#end for

[J.O. Aho]

Post by Lawrence D'Oliveiro

Post by J.O. Aho

Post by Lawrence D'Oliveiro

I would looked into using parameterized queries ...

There are lots of cases they don’t handle. Like for example LIKE and
REGEXP operands.

select * from table where column1 like ?;

Like: you want to do a partial match on what the user typed. And what
the user typed can include characters like “%” and “_”, which you
don’t want to be mistaken for wildcards.
Another example: can your parameterized queries handle dynamic SQL
like this?

did you try to wrap it into a stored procedure?

[Lawrence D'Oliveiro]

Post by J.O. Aho

Another example: can your parameterized queries handle dynamic SQL like
this?

did you try to wrap it into a stored procedure?

That’s a “no”, then.