Preface
Recently, when using the where in statement in SQL, some unexpected query results were caused. Especially when writing and executing SQL statements in the code, some unexpected situations will occur. After checking some information and manual tests, I found that there was a problem with the writing method of my SQL statement, so I recorded it here.
example
For business requirements, you need to query database records with domain name fields within the scope ("", "", "",") from the asset table through SQL statements. How to write SQL statements?
Splicing method (Error)
values = "'','',''"
sql = "select * from asset where domain in ("+values+")"
print sql
Note: By constructing SQL statements in string splicing, it can be passed syntactically, but there are security risks (refer to SQL injection vulnerability)
Parameterization 1 (Error)
values = (("","",""),)
sql = "select * from asset where domain in %s"
print sql
print values
Note: Through parameterization, pass the query content after where in. On the surface, it seems to be fine, but during the compilation process, ("","",") will be regarded as a string as a whole, and as a query condition, it does not match the requirements.
Parameterization 2 (correct)
values = ("","","")
sql = "select * from asset where domain in ({})".format(",".join(['%s' for i in values]))
print sql
print values
Description: Dynamically construct compiled parameters by calculating the number of strings in values.
Summarize
The above is the entire content of this article. I hope that the content of this article has certain reference value for your study or work. Thank you for your support.