带零到许多参数的子句中的SQL [英] SQL In Clause with Zero to Many Parameters

问题描述

我有一个SQL查询，该查询由非常有限的内部框架进行参数化.查询看起来像这样:

I have a SQL query which is parameterized by a very limited in-house framework. The query looks like this:

Select * from somewhere
where name IN (:parameter);

代码会将零到多个字符串注入到:parameter指定的位置. :parameter"标志只能在"IN"子句中使用(因此不能在where子句之后移动以有条件地插入"name IN").

The code will inject zero to many strings into the location specified by :parameter. The ":parameter" flag can only be used within the "IN" clause (so it can't be moved after the where clause to conditionally insert the 'name IN') section.

有时用户会将参数设置为:

Sometimes the user will set parameter to:

狗"，猫"

其他时候，用户不会将任何值放入:parameter变量中.这会导致问题，因为生成的SQL查询将是:

Other times, the user will not put any values into the :parameter variable. This causes a problem since the resulting SQL query will be:

Select * from somewhere
where name IN ();

我的代码可以捕获参数为空的情况，但是我需要将一些东西注入IN语句中，以确保永远不会与实际字符串匹配.

My code can catch the case where parameter is empty, but I need something which I can inject into the IN statement which is guaranteed to NEVER match an actual string.

有没有我可以插入的SQL正则表达式，它从不与任何字符串匹配?诸如％.％之类的东西....

Is there any SQL regular expression which I could inject which would NEVER match any string? Something like %.% or something....

谢谢！

推荐答案

您可以说:

where name in (null)

这将永远不会匹配，因为任何东西都不等于null(甚至不等于null本身.)

This will never match, since nothing is equal to null (not even null itself.)

这篇关于带零到许多参数的子句中的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！