SQL Statement Match Anything

Question

I use a regex in my SQL statements for an app that look like this

SELECT * FROM table WHERE id = {{REPLACEME}}

However, sometimes I'm not giving a parameter to replace that string with. Is there a way to replace it with something that matches anything. I tried *, but that does not work.

Answer 1

1. SELECT * FROM table WHERE id = id will match all rows that have non-null id
2. SELECT * FROM table WHERE id = id OR id IS NULL will match all rows.

id is probably a primary key, so you can probably use the former.

Answer 2

I can only describe my solution with an example. The @AllRec is a parameter:

Declare @AllRec bit
set @AllRec = {0|1}  --as appropriate

SELECT * 
FROM table 
WHERE 
   (
       id = {{REPLACEME}}
       and @AllRec = 0
   ) OR (
       @AllRec = 1
   )

In this solution, if @AllRec is 1 then everything is returned, ignoring the id filter. If @AllRec is zero, then the id filter is applied and you get one row. You should be able to quickly adapt this to your current regex solution.

Answer 3

Replace {{REPLACEME}} with

[someValidValueForYouIdType] OR 1=1

Answer 4

Using the Regex-Replace option opens you up to SQL Injection attacks.

Assuming your language has support for parameterized queries, try this modified version of Jacob's answer:

SELECT * FROM table WHERE (id = @id OR @id IS NULL)

The catch is that you'll always have to provide the @id value.

Answer 5

Not sure what language your using, and this code kind of scares me but...

var statement = "SELECT * FROM table";

If REPLACEME is not empty Then
   statement += " WHERE id = {{REPLACEME}}"
End If

Answer 6

SELECT field1, field2
FROM dbo.yourTable
WHERE id = CASE WHEN @var IS NULL THEN
               id
           ELSE
               @var
           END