How to realize WHERE IN Parametric compilation in SQL

This article mainly shows you "how to achieve WHERE IN parametric compilation in SQL", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to achieve WHERE IN parametric compilation in SQL" this article.

Examples

For business requirements, you need to query the database records with domain name fields in the range of ("thief.one", "nmask.cn", "sec.thief.one") from the asset asset table through the SQL statement. How to write the SQL statement?

Splicing (error)

Values = "'thief.one','nmask.cn','sec.thief.one'" sql = "select * from asset where domain in (" + values+ ")" print sql

Description: by constructing sql statements by string concatenation of search conditions, the syntax can be passed, but there are security risks (see sql injection vulnerability)

Parameterization 1 (error)

Values = ("thief.one", "nmask.cn", "sec.thief.one"),) sql = "select * from asset where domain in% s" print sqlprint values

Description: pass in the query content after where in by parameterization. On the face of it, there is no problem, but in the compilation process, ("thief.one", "nmask.cn", "sec.thief.one") as a whole will be regarded as a string, and as a query condition, does not meet the requirements.

Parameterization 2 (correct)

Values = ("thief.one", "nmask.cn", "sec.thief.one") sql = "select * from asset where domain in ({})" .format ("," .join (['% s' for i in values])) print sqlprint values

Description: by calculating the number of strings in values, dynamically construct the compiled parameters.

These are all the contents of the article "how to achieve WHERE IN Parametric compilation in SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!