Bug #117087 Unable to escape a parameter marker (`%s`) used in a query that should not be treated as a parameter marker

• Submitted: 31 Dec 2024 16:59
• Modified: 11 Mar 0:55
• Reporter: Thomas Rambø (OCA)
• Status: Closed
• Category: Connector / Python
• Severity: S3 (Non-critical)
• Version: 9.1.0
• OS: Linux (Arch Linux)
• CPU Architecture: x86

[31 Dec 2024 16:59] Thomas Rambø

Description:
I want to execute the following query, together with a parameter binding:

cur.execute(
    'select date_format(%s, "%Y-%m-%d %H:%i:%s")',
    ("2017-06-15 12:20:23", ),
)

This results in an error:

mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

The parameter marker looks like a C printf-style variant, so I try to escape the `%s` by using `%%s` instead:

cur.execute(
    'select date_format(%s, "%Y-%m-%d %H:%i:%%s")',
    ("2017-06-15 12:20:23", ),
)

But it still leads to the same error message.

How can I include the date format string in a query like the above? What is the correct way to escape the parameter marker? It would seem that the usual way when escaping C printf-style markers does not work.

mysql-connector-python version: 9.1.0
Python version: 3.13.1
MySQL version: 9.1.0

How to repeat:
import mysql.connector

def main():
    # Connect to server
    cnx = mysql.connector.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        password="root",
    )

    # Get a cursor
    cur = cnx.cursor()

    # Execute a query
    cur.execute(
        'select date_format(%s, "%Y-%m-%d %H:%i:%%s")',
        ("2017-06-15 12:20:23", ),
    )

    # Fetch one result
    row = cur.fetchone()
    print(row[0])

    # Close connection
    cnx.close()

if __name__ == "__main__":
    main()

Suggested fix:
Recognize when a parameter marker is escaped

[6 Jan 8:29] MySQL Verification Team

Hello Thomas Rambø,

Thank you for the report and feedback.

regards,
Umesh

[10 Mar 12:53] Souma Kanti Ghosh

Posted by developer:
 
Hello Thomas,

Thanks for raising this bug and your suggestion helped a lot while fixing it too.

Cheers,
Souma Kanti Ghosh

[11 Mar 0:55] Daniel So

Posted by developer:
 
Added the following entry to the Connector/Python 9.3.0 changelog: 

"It was not possible to escape the string "%s" used in a query when it should not be treated as a parameter marker. This patch adds proper regular expression checks to escape the "%s" parameter marker when "%%s" is used in the query."