關於MySQL的1064錯誤

標籤：ORC   user   ast   這一   exec   驗證   another   sql注入   page   

MySQL的1064錯誤是SQL語句寫的有問題時出現的，即SQL的語法錯誤。筆者常常使用MySQL-python這個庫來對MySQL進行操作，代碼中報這個錯誤的一般是cursor.execute(sql, param)這一行。

這種參數式執行SQL語句的用法可以有效防止SQL注入的安全問題，但是為什麼MySQL會報錯呢？如果你確認SQL寫的沒問題，檢查一下SQL語句中是否使用了引號。

在使用cursor.execute(sql, param)時，MySQL-python庫會自動轉義含有%s的字串，所以不要畫蛇添足在SQL語句中給%s加引號了，會報1064的錯誤滴！

另外也有許多人使用有SQL注入隱患的cursor.execute(sql % param)這種用法，這樣是可以給%s加引號的。

但是安全問題孰重孰輕，相信各位自有判斷。

在使用pymysql對mysql進行操作時，使用%s給excute傳入參數時出錯，錯誤碼如下：

table="huxing_table"key="house_structure_page_url"value="test"cursor=db.cursor()cursor.execute("INSERT INTO %s (%s) VALUES(%s)",(table,key,value))db.commit()cursor.close()

 

錯誤提示為：

Traceback (most recent call last):  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 112, in execute    result = self._query(query)  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 230, in _query    conn.query(q)  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 607, in query    self._affected_rows = self._read_query_result()  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 691, in _read_query_result    result.read()  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 869, in read    self.first_packet = self.connection.read_packet()  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 686, in read_packet    packet.check_error()  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 328, in check_error    raise_mysql_exception(self.__data)  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/err.py", line 142, in raise_mysql_exception    _check_mysql_exception(errinfo)  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/err.py", line 135, in _check_mysql_exception    raise errorclass(errno,errorvalue)pymysql.err.ProgrammingError: (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 ‘‘huxing_table‘ (‘house_structure_page_url‘) VALUES(‘test‘)‘ at line 1")During handling of the above exception, another exception occurred:Traceback (most recent call last):  File "/Users/huangjing/downHouseInfo/MainF.py", line 238, in <module>    cursor.execute("INSERT INTO %s (%s) VALUES(%s)",(table,key,value))  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 117, in execute    self.errorhandler(self, exc, value)  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 189, in defaulterrorhandler    raise errorclass(errorvalue)pymysql.err.ProgrammingError: (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 ‘‘huxing_table‘ (‘house_structure_page_url‘) VALUES(‘test‘)‘ at line 1")Exception ignored in: <bound method Cursor.__del__ of <pymysql.cursors.Cursor object at 0x10585ebe0>>Traceback (most recent call last):  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 41, in __del__  File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 47, in closeReferenceError: weakly-referenced object no longer exists

 

但是，嘗試執行

cursor.execute("INSERT INTO huxing_table (house_structure_page_url) VALUES(%s)",(value))

 

時，沒有錯誤提示。

在錯誤提示第31行發現，執行的mysql語句中用%s替換的參數外加上了單引號。

‘‘huxing_table‘ (‘house_structure_page_url‘) VALUES(‘test‘)‘

 

在mysql命令列終端進行測試，執行語句

mysql> insert into huxing_table (`house_structure_page_url`) values("test");Query OK, 1 row affected (0.00 sec)

 

沒有錯誤提示。而執行

mysql> insert into huxing_table (‘house_structure_page_url‘) values("test");ERROR 1064 (42000): 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 ‘‘house_structure_page_url‘) values("test")‘ at line 1

 

則有錯誤提示。再進行驗證

mysql> insert into huxing_table (house_structure_page_url) values(‘test‘);Query OK, 1 row affected (0.00 sec)

 

不出錯。

mysql> insert into ‘huxing_table‘ (house_structure_page_url) values("test");ERROR 1064 (42000): 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 ‘‘huxing_table‘ (house_structure_page_url) values("test")‘ at line 1

 

出錯，說明在mysql的insert語句中表名和列名外都不能加單引號，而值則可以加單引號。

就直接寫語句好了。
最後的解決辦法是插入一條資料寫一條sql語句。

關於MySQL的1064錯誤