1. Forum
  2. Usenet
  3. COMP.DATABASES.MYSQL

  • Escaping Strings

    From Lawrence D'Oliveiro@21:1/5 to All on Mon Jul 15 22:46:15 2024
    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("'", "''") + "'"

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From J.O. Aho@21:1/5 to Lawrence D'Oliveiro on Mon Aug 19 11:24:06 2024
    On 16/07/2024 00.46, Lawrence D'Oliveiro wrote:
    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("'", "''") + "'"

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

    --
    //Aho

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lawrence D'Oliveiro@21:1/5 to J.O. Aho on Fri Aug 23 02:33:36 2024
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From J.O. Aho@21:1/5 to Lawrence D'Oliveiro on Fri Aug 23 07:52:38 2024
    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 ?;

    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.

    --
    //Aho

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lawrence D'Oliveiro@21:1/5 to J.O. Aho on Sat Aug 24 22:49:38 2024
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From J.O. Aho@21:1/5 to Lawrence D'Oliveiro on Sun Aug 25 09:27:30 2024
    On 25/08/2024 00.49, Lawrence D'Oliveiro wrote:
    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?

    did you try to wrap it into a stored procedure?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lawrence D'Oliveiro@21:1/5 to J.O. Aho on Thu Sep 5 06:11:19 2024
    On Sun, 25 Aug 2024 09:27:30 +0200, J.O. Aho wrote:

    On 25/08/2024 00.49, Lawrence D'Oliveiro wrote:

    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)