Prepared statement pymysql谁是正确的？

Question 1

我创建了一个名为test的测试数据库，里面有一个名为testTable的表，有一个自动递增的id值和一个需要varchar(30)的name域。

PREPARE语句查询（4个）在复制到phpmyadmin中时执行正常，但我得到了错误👍 2021-01-08 18:26:53,022 (MainThread) [ERROR] (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET\n @name = 'fred';\nEXECUTE\n statement USING @name;\nDEALLOCATE\nPREPARE\n' at line 5" )

The test code:

import pymysql
import logging
class TestClass():
    def __init__(self):
        # mysqlconnections
        self.mySQLHostName = "localhost" 
        self.mySQLHostPort = 3306
        self.mySQLuserName = "userName"
        self.mySQLpassword = "pass"
        self.MySQLauthchandb = "mysql"
    def QueryMYSQL (self, query):
            #logging.info("QueryMYSQL  : " + str( query)) # Uncomment to print all mysql queries sent
            conn = pymysql.connect(host=self.mySQLHostName, port=self.mySQLHostPort, user=self.mySQLuserName, passwd=self.mySQLpassword, db=self.MySQLauthchandb, charset='utf8')
            conn.autocommit(True)
            cursor = conn.cursor()
            if cursor:
                returnSuccess = cursor.execute(query)
            if cursor:
                returnValue = cursor.fetchall()
            #logging.info ("return value : " + str(returnValue)) # Uncomment to print all returned mysql queries
            if cursor:
                cursor.close()
            if conn:
                conn.close()
            return returnValue
        except Exception as e:
            logging.error("Problem in ConnectTomySQL")
            logging.error(query)
            logging.error(e)
            return False
# Default error logging log file location:
logging.basicConfig(format='%(asctime)s (%(threadName)-10s) [%(levelname)s] %(message)s', filename= 'ERROR.log',filemode = "w", level=logging.DEBUG)
logging.info("Logging Started")
test = TestClass()
result = test.QueryMYSQL("Describe test.testTable")
print(result)
query = """
PREPARE
    statement
    'INSERT INTO test.testTable (id, name) VALUES (NULL , ?)';
    @name = 'fred';
EXECUTE
    statement USING @name;
DEALLOCATE
PREPARE
    statement;
result = test.QueryMYSQL(query)
print(result)
我想这是一个库的问题，而不是一个mysql的问题？我试图使用准备好的语句来防止用户输入的代码注入，因为我知道准备好的语句是做到这一点的最好方法，而不是试图预先过滤用户输入而错过一些东西。
我在github上问了这个问题，但其中一位作者（甲烷-稻田直树）这样回答。
========
当存在查询注入漏洞时，多语句可以被攻击者使用。因此，它在默认情况下被禁用。
as I understand this prepared statements are the best way
你完全错了。你对准备好的语句的使用根本不能保护你免受SQL注入。如果你启用了多语句，你的 "准备好的语句 "就会受到SQL注入的攻击。
但我不是免费的技术支持，也不是你的免费老师。开放源码软件的维护者也不是。请不要在这里问。
和他关闭了这个问题。
Is he correct?
我正在读的作者Robin Nixon的书，"Learning PHP, MySQL and JavaScript" O'Reilly第五版。他似乎有一个误解，我引用 "让我介绍一下与MySQL交互的最佳和推荐的方式，这在安全方面几乎是无懈可击的"，是在第260页的使用占位符部分。他错了吗？
因为我买这本书是为了改善我的安全实践，现在我不确定什么是正确的。

Question 2


          
           
            我从pymysql的开发者那里了解到，该库不支持PREPARE mysql语句。另外，pymysql库默认不执行多语句。
           
           
            我明白，如果启用多语句，我第一次尝试将数值替换到INSERT语句中，本身就是不安全的。这可以通过在连接构造函数中使用client_flag=pymysql.constants.CLIENT.MULTI_STATEMENTS来实现。
           
           
            然而，pymysql库确实允许使用cursor.execute(query, (tuple))方法在MySQL查询中使用占位符。
           
           
            为了证明这一点，我写了以下测试代码的例子。
           
           import pymysql
import logging
class TestClass():
    def __init__(self):
        # mysqlconnections
        self.mySQLHostName = "localhost"
        self.mySQLHostPort = 3306
        self.mySQLuserName = "name"
        self.mySQLpassword = "pw"
        self.MySQLauthchandb = "mysql"
    def QueryMYSQL (self, query, data = ()):
            logging.info("QueryMYSQL  : " + str( query)) # Uncomment to print all mysql queries sent
            conn = pymysql.connect(host=self.mySQLHostName, port=self.mySQLHostPort, user=self.mySQLuserName, passwd=self.mySQLpassword, db=self.MySQLauthchandb, charset='utf8', client_flag=pymysql.constants.CLIENT.MULTI_STATEMENTS) #code injection requires multistatements to be allowed this is off in pymysql by default and has to be set on manually. 
            conn.autocommit(True)
            cursor = conn.cursor()
            if cursor:
                if data:
                    returnSuccess = cursor.execute(query, data)
                else:
                    returnSuccess = cursor.execute(query)
            if cursor:
                returnValue = cursor.fetchall()
            logging.info ("return value : " + str(returnValue)) # Uncomment to print all returned mysql queries
            if cursor:
                cursor.close()
            if conn:
                conn.close()
            return returnValue
        except Exception as e:
            logging.error("Problem in ConnectTomySQL")
            logging.error(e)
            logging.error(query)
            if data:
                logging.error("Data {}".format(str(data)))
            return False
# Default error logging log file location:
logging.basicConfig(format='%(asctime)s (%(threadName)-10s) [%(levelname)s] %(message)s', filename= 'ERROR.log',filemode = "w", level=logging.DEBUG)
logging.info("Logging Started")
def usePlaceholder(userInput):
    query = "INSERT INTO test.testTable (id, name) VALUES (NULL , %s)"
    data = (userInput,) 
    result = test.QueryMYSQL(query,data)
    print(result)
def useSubstitution(userInput):
    query = "INSERT INTO test.testTable (id, name) VALUES (NULL , '{}')".format(userInput) # this is unsafe.
    result = test.QueryMYSQL(query)
    print(result)
test = TestClass()
#Create the test database and testTable.
query = "CREATE DATABASE test"
test.QueryMYSQL(query)
query = "CREATE TABLE `test`.`testTable` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(256) NULL DEFAULT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;"
test.QueryMYSQL(query)
#Simulated user input.
legitUserEntry = "Ringo"
injectionAttempt = "333' ); INSERT INTO test.testTable (id, name) VALUES (NULL , 666);#" #A simulated user sql injection attempt.
useSubstitution(legitUserEntry) # this will also insert Ringo - but could be unsafe.
usePlaceholder(legitUserEntry) # this will insert Ringo - but is safer.