Python MySQL查询使用DEFAULT值出现SQL语法错误，求排查

Problem Context #

You've created an ip table with the following structure:

CREATE TABLE `ip` ( 
  `idip` int(11) NOT NULL AUTO_INCREMENT, 
  `ip` decimal(45,0) DEFAULT NULL, 
  `mask` int(11) DEFAULT NULL, 
  PRIMARY KEY (`idip`), 
  UNIQUE KEY `ip_UNIQUE` (`ip`) 
)

And you're trying to run this Python query to fetch a record:

sql = "select idip from ip where ip=%s and mask=%s" % (long(next_hop), 'DEFAULT')
cursor.execute(sql)
idnext_hop = cursor.fetchone()[0]

But you're hitting this syntax error:

Inserting routes into table routes (1/377)...('insere_tabela_routes: Error on insertion at table routes, - SQL: ', 'select idip from ip where ip=0 and mask=DEFAULT') 
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 '' at line 1

Root Cause #

Let's break down what's going wrong here:

• Misuse of the DEFAULT keyword: In MySQL, DEFAULT is meant for setting a column to its predefined default value during INSERT/UPDATE operations. It's not valid syntax to use mask=DEFAULT in a WHERE clause to match rows where the column uses its default value. Since your mask column's default is NULL, you need to check for NULL explicitly instead.
• Risky SQL string formatting: Using string interpolation to inject 'DEFAULT' into your query creates the invalid mask=DEFAULT clause. Beyond syntax issues, this approach also exposes your code to SQL injection vulnerabilities.

Solution #

First, fix the query logic to target rows where mask is NULL (its actual default value). Second, use parameterized queries—the safe, standard way to pass values to SQL in Python—instead of manual string formatting.

Here's the corrected code:

# Query for rows where mask uses its default NULL value
sql = "select idip from ip where ip=%s and mask IS NULL"
# Pass parameters as a tuple to execute() - no string formatting needed
cursor.execute(sql, (long(next_hop),))
idnext_hop = cursor.fetchone()[0]

Why This Works #

• mask IS NULL correctly identifies rows where the mask column hasn't been assigned a value (so it uses its default NULL).
• Parameterized queries handle value escaping automatically, eliminate SQL injection risks, and avoid syntax errors caused by manual string manipulation.

内容的提问来源于stack exchange，提问作者Bruna Zamith