MULTI_STATEMENTS & MULTI_RESULTS always enabled in client_flag in Connection #590

mjmaenpaa · 2017-08-02T13:11:59Z

MULTI_STATEMENTS & MULTI_RESULTS are always enabled in Connection client_flag and there is no way to disable them. client_flag parameter is always bitwise or'd with constants.CLIENT.CAPABILITIES which includes MULTI_STATEMENTS & MULTI_RESULTS.
In connections.py: client_flag |= CLIENT.CAPABILITIES

Small example to demonstrate issue:

from __future__ import print_function

import pymysql
from pymysql.constants.CLIENT import MULTI_STATEMENTS

db_prefix = "bugtest_"
host = "127.0.0.1"
port = 3306
user = "root"
password = None

def create_db(client_flag):
    try:
        connection = pymysql.connect(user=user, host=host, port=port, passwd=password, client_flag=client_flag)
        cursor = connection.cursor()

        db1 = db_prefix + "1"
        db2 = db_prefix + "2"
        sql = 'CREATE DATABASE IF NOT EXISTS {0:s}; CREATE DATABASE IF NOT EXISTS {1:s};'.format(db1, db2)
        print('  ' + sql)
        cursor.execute(sql)
        cursor.execute("SHOW DATABASES LIKE 'bugtest_%'")
        results = cursor.fetchall()
        for result in results:
            print('  ' + result[0])
    finally:
        cursor.execute('DROP DATABASE IF EXISTS {0:s}'.format(db1))
        cursor.execute('DROP DATABASE IF EXISTS {0:s}'.format(db2))
        connection.close()

print('This query should go okay.')
create_db(MULTI_STATEMENTS)        
        
print('This should give database error')
create_db(0)

And what happens currently:

This query should go okay.
  CREATE DATABASE IF NOT EXISTS bugtest_1; CREATE DATABASE IF NOT EXISTS bugtest_2;
  bugtest_1
  bugtest_2
This should give database error
  CREATE DATABASE IF NOT EXISTS bugtest_1; CREATE DATABASE IF NOT EXISTS bugtest_2;
  bugtest_1
  bugtest_2

And what happens if MULTI_STATEMENTS is removed from CAPABILITIES:

This query should go okay.
  CREATE DATABASE IF NOT EXISTS bugtest_1; CREATE DATABASE IF NOT EXISTS bugtest_2;
  bugtest_1
  bugtest_2
This should give database error
  CREATE DATABASE IF NOT EXISTS bugtest_1; CREATE DATABASE IF NOT EXISTS bugtest_2;
C:\Python27\lib\site-packages\pymysql\cursors.py:166: Warning: (1008, "Can't drop database 'bugtest_1'; database doesn't exist")
  result = self._query(query)
C:\Python27\lib\site-packages\pymysql\cursors.py:166: Warning: (1008, "Can't drop database 'bugtest_2'; database doesn't exist")
  result = self._query(query)
Traceback (most recent call last):
  File "bug.py", line 35, in <module>
    create_db(0)
  File "bug.py", line 21, in create_db
    cursor.execute(sql)
  File "C:\Python27\lib\site-packages\pymysql\cursors.py", line 166, in execute
    result = self._query(query)
  File "C:\Python27\lib\site-packages\pymysql\cursors.py", line 322, in _query
    conn.query(q)
  File "C:\Python27\lib\site-packages\pymysql\connections.py", line 862, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Python27\lib\site-packages\pymysql\connections.py", line 1063, in _read_query_result
    result.read()
  File "C:\Python27\lib\site-packages\pymysql\connections.py", line 1346, in read
    first_packet = self.connection._read_packet()
  File "C:\Python27\lib\site-packages\pymysql\connections.py", line 1020, in _read_packet
    packet.check_error()
  File "C:\Python27\lib\site-packages\pymysql\connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "C:\Python27\lib\site-packages\pymysql\err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE DATABASE IF NOT EXISTS bugtest_2' at line 1")

The text was updated successfully, but these errors were encountered:

elemount · 2017-08-03T01:59:02Z

Hi @mjmaenpaa , what's your scenario on the MULTI_STATEMENTS & MULTI_RESULTS, why you need to disable it? It is not usual to disable it? Could you describe your scenario?

mjmaenpaa · 2017-08-04T06:24:52Z

Hi,

main reason I needed to disable them was because I was doing security testing for my application and simplest way to detect multi statement sql injection was to simply disable multi statement support in connection.

But I managed to disable it by monkey patching before creating connection:

orig_capabilities = pymysql.constants.CLIENT.CAPABILITIES
pymysql.constants.CLIENT.CAPABILITIES ^= pymysql.constants.CLIENT.MULTI_STATEMENTS
conn = pymysql.connect()
pymysql.constants.CLIENT.CAPABILITIES = orig_capabilities

elemount · 2017-08-10T05:32:56Z

@mjmaenpaa , I would suggest you just set

conn = pymysql.connect(defer_connect=True)
conn.client_flag &= ~pymysql.constants.CLIENT.MULTI_STATEMENTS
conn.connect()

This is a perfect way to do it by set per connections.

elemount · 2017-08-10T05:35:38Z

@mjmaenpaa , and I do not think we can modify client_flag parameter behavior. @methane , what is your idea?

methane · 2017-08-10T05:38:56Z

I think default should be same to MySQL Connector/C

elemount · 2017-08-10T05:49:18Z

@methane , In MySQL Connector/C, the MULTI_STATEMENTS attribute is disabled by default, so it can be opened by set client_flag. Refer mysql-real-connect API.
I do not think this attribute can be disabled in PyMySQL, which may broke so may existing program using PyMySQL.

Or should we put

conn = pymysql.connect(defer_connect=True)
conn.client_flag &= ~pymysql.constants.CLIENT.MULTI_STATEMENTS
conn.connect()

as FAQ about how to disable this attributes?

methane · 2017-08-10T06:31:23Z

I do not think this attribute can be disabled in PyMySQL, which may broke so may existing program using PyMySQL.

I will do it at 0.8. Release note can have information about that.
While it may break existing program, it won't corrupt data, only show errors.
Users who see the error can fix the program.

In PyMySQL the "MULTI_STATEMENT" flag has been disabled by default. (see PyMySQL/PyMySQL#590 ) Migration 98 had a single string with multiple statements ran in a single execution. Moving this to multiple executions of the same statements allows the migration to succeed with the new behaviour. Caused-By: PyMySQL/PyMySQL@c0aa317 Change-Id: I5f6d92f695c4c5830b8595b0cecbbafb426470a1

* Update designate from branch 'master' - PyMySQL 0.8.0 compat fix In PyMySQL the "MULTI_STATEMENT" flag has been disabled by default. (see PyMySQL/PyMySQL#590 ) Migration 98 had a single string with multiple statements ran in a single execution. Moving this to multiple executions of the same statements allows the migration to succeed with the new behaviour. Caused-By: PyMySQL/PyMySQL@c0aa317 Change-Id: I5f6d92f695c4c5830b8595b0cecbbafb426470a1

methane · 2018-01-10T16:46:37Z

You must not ask a question on issue tracker

In PyMySQL the "MULTI_STATEMENT" flag has been disabled by default. (see PyMySQL/PyMySQL#590 ) Migration 98 had a single string with multiple statements ran in a single execution. Moving this to multiple executions of the same statements allows the migration to succeed with the new behaviour. Caused-By: PyMySQL/PyMySQL@c0aa317 Change-Id: I5f6d92f695c4c5830b8595b0cecbbafb426470a1

methane closed this as completed in c0aa317 Dec 20, 2017

github-actions bot locked as resolved and limited conversation to collaborators Aug 3, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MULTI_STATEMENTS & MULTI_RESULTS always enabled in client_flag in Connection #590

MULTI_STATEMENTS & MULTI_RESULTS always enabled in client_flag in Connection #590

MULTI_STATEMENTS & MULTI_RESULTS always enabled in client_flag in Connection #590

MULTI_STATEMENTS & MULTI_RESULTS always enabled in client_flag in Connection #590

Comments