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

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)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	9.1.0	OS:	Linux (Arch Linux)
Assigned to:		CPU Architecture:	x86

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

Hello Thomas Rambø,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Hello Thomas,

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

Cheers,
Souma Kanti Ghosh

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

Posted by developer:
 
This bug is being re-opened as the fix done in 9.3.0 was rolled back in 9.4.0.
The solution of trying to escape a format-style query placeholder (%s) with a double percentage sign "%%" is an incorrect fix.
This bug needs to be fixed by not treating any "%s" inside string literals as query placeholders, string literals must be left unchanged and never changed by the connector.

After this bug is fixed, there will be no escaping needed for any "%s" inside any string literal, so the DATE_FORMAT function must work as expected with a "%s" to format seconds.