Re: Escaping Strings

On Fri, 23 Aug 2024 07:52:38 +0200, J.O. Aho wrote:

> On 23/08/2024 04.33, Lawrence D'Oliveiro wrote:
>
>> On Mon, 19 Aug 2024 11:24:06 +0200, J.O. Aho wrote:
>> 
>>> 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 ?;

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