Categories
dynamic-sql sql

Why would someone use WHERE 1=1 AND in a SQL clause?

304

Why would someone use WHERE 1=1 AND <conditions> in a SQL clause (Either SQL obtained through concatenated strings, either view definition)

I’ve seen somewhere that this would be used to protect against SQL Injection, but it seems very weird.

If there is injection WHERE 1 = 1 AND injected OR 1=1 would have the same result as injected OR 1=1.

Later edit: What about the usage in a view definition?


Thank you for your answers.

Still,
I don’t understand why would someone use this construction for defining a view, or use it inside a stored procedure.

Take this for example:

CREATE VIEW vTest AS
SELECT FROM Table WHERE 1=1 AND table.Field=Value

2

  • 4

    “why would someone use this construction for defining a view” Probably out of habit. It offers no functional advantage in static queries.

    – ADTC

    Jul 30, 2014 at 5:47

  • Nobody should use Dynamic SQL

    – user2188550

    Oct 15, 2019 at 19:24

389

If the list of conditions is not known at compile time and is instead built at run time, you don’t have to worry about whether you have one or more than one condition. You can generate them all like:

and <condition>

and concatenate them all together. With the 1=1 at the start, the initial and has something to associate with.

I’ve never seen this used for any kind of injection protection, as you say it doesn’t seem like it would help much. I have seen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.

17

  • 38

    Sometimes is not about being lazy, but having a cleaner code.

    Oct 28, 2008 at 10:55

  • 41

    dealing with trailing ANDs or COMMAs isn’t dirty… nothing is cleaner by having 1=1 all over your SQL.

    – Mark Brady

    Oct 28, 2008 at 22:01

  • 21

    DBAs? What are they for? 🙂

    Oct 29, 2008 at 17:22

  • 41

    DBA’s are there to clean up after programmers who think they know how to use databases effectively.

    Dec 11, 2009 at 10:46

  • 27

    “Lazy” I like to think it’s smart, not lazy. You’re avoiding repetitive code and unnecessary condition checks. Without being able to add where 1=1 (Oracle) or where true (Postgres), I will have to check for each condition whether it is the first one. There is no point in doing so, and it only adds more boilerplate code.

    – ADTC

    Jul 30, 2014 at 5:58

127

Just adding a example code to Greg’s answer:

dim sqlstmt as new StringBuilder
sqlstmt.add("SELECT * FROM Products")
sqlstmt.add(" WHERE 1=1") 

''// From now on you don't have to worry if you must 
''// append AND or WHERE because you know the WHERE is there
If ProductCategoryID <> 0 then
  sqlstmt.AppendFormat(" AND ProductCategoryID = {0}", trim(ProductCategoryID))
end if
If MinimunPrice > 0 then
  sqlstmt.AppendFormat(" AND Price >= {0}", trim(MinimunPrice))
end if

2

  • 7

    bit hacky, but seems like a valid use.

    – Mike

    Dec 11, 2009 at 10:04

  • 5

    This should be the accepted answer. The practice is really only hack around to not having to determine how many conditionals you have.

    – aglassman

    Apr 9, 2013 at 19:05

40

I’ve seen it used when the number of conditions can be variable.

You can concatenate conditions using an ” AND ” string. Then, instead of counting the number of conditions you’re passing in, you place a “WHERE 1=1” at the end of your stock SQL statement and throw on the concatenated conditions.

Basically, it saves you having to do a test for conditions and then add a “WHERE” string before them.

0