question

chingsun-0116 avatar image
0 Votes"
chingsun-0116 asked chingsun-0116 commented

SSISDB Catalog View: ...OR (0 = 1) what does it mean?

Hi,
I am not very experienced in t-sql. Recently working on SSIS projects, and found SSISDB Catalog related views, all ended with similar syntax as below. Not sure what "0=1" means, did some research, not luck. Ended guessing everyone would have permission for this view...
Can anyone help me out? Thanks,

ALTER VIEW [catalog].[environment_references]
AS
SELECT .......
WHERE [project_id] in (SELECT [id] FROM [internal].[current_user_readable_projects])
OR (IS_MEMBER('ssis_admin') = 1)
OR (IS_SRVROLEMEMBER('sysadmin') = 1)
OR (0 = 1)

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered chingsun-0116 commented

Hi @chingsun-0116,

For dynamic queries, you may take a reference:

https://www.sqlshack.com/dynamic-sql-in-sql-server/

As far as I known about t-sql.

For where 1=1 is true proposition, the query will return all the values.

For where 0=1 is pseudo-proposition, can be used to manage the OR conditions in the query.

You may also refer this which try to explain the two statement.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Zoehui-MSFT for all your help and suggestions. I will do some studies per your links.

1 Vote 1 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @chingsun-0116,

From t-sql side, A query like this can be used to ping the database.

 WHERE 1=0

A query like that can test for:

Server availability

CUST_ATTR49 table existence

ID column existence

Keeping a connection alive

Cause a trigger to fire without changing any rows (with the where clause, but not in a select query)

Manage many OR conditions in dynamic queries (e.g WHERE 1=0 OR <condition>)

Details you may refer: why-would-you-use-where-1-0-statement-in-sql

For catalog.environment_references, you may see here.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

chingsun-0116 avatar image
0 Votes"
chingsun-0116 answered ZoeHui-MSFT commented

Thank Zoe very much for the quick response. It would expand my research scope. But there is the subtle difference between the answer and my question:
My question is for " (0 = 1)", but the answer is for “1=0". Any idea on this difference?

My question as in my original post, is from SSISDB catalog view (scripted code). Seemed to me is more related to permissions for the view, or the way to control returning data. I also saw some variant code somewhere for this as "OR (1=1)". But don’t have much idea on both when used with SSISDB catalog view...

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @chingsun-0116,
Per my understanding, " (0 = 1)" has no difference with “1=0". it is used to manage OR conditions in dynamic queries for the code integrity.

1 Vote 1 ·
IgorGelin-0063 avatar image
0 Votes"
IgorGelin-0063 answered

It is possible that the SQL code was created dynamically. In this code a condition was omitted and replaced by (0=1) expression.

HTH,

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
0 Votes"
DanGuzman answered

I think @IG-0063 's theory is correct. The hard-coded predicate constants in the views (`(0 = 1)`,`(1 = 0)`,`(1 = 1)`) are likely artifacts of the T-SQL code generation tooling used to create SSIS catalog objects. These constants will never change after the view is created so they are not used to dynamically control the rows returned (e.g. not used for permissions). SSIS catalog implementation details like this should be ignored.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

chingsun-0116 avatar image
0 Votes"
chingsun-0116 answered chingsun-0116 commented

Thanks for all the input. Any good documents for the "dynamic queries", that I am not really familiar with.

@DanGuzman brought out a good point, "These constants will never change after the view is created so they are not used to dynamically control the rows returned (e.g. not used for permissions)". The code I am working with intends to "alter view". Will it make the return data differently if alter the view replacing "OR (0=1)" with OR (1=1)"? Detail as below:

Original code:
ALTER VIEW [catalog].[environment_references]
AS
SELECT .......
WHERE [project_id] in (SELECT [id] FROM [internal].[current_user_readable_projects])
OR (IS_MEMBER('ssis_admin') = 1)
OR (IS_SRVROLEMEMBER('sysadmin') = 1)
OR (0 = 1)

Proposed code:
ALTER VIEW [catalog].[environment_references]
AS
SELECT .......
WHERE [project_id] in (SELECT [id] FROM [internal].[current_user_readable_projects])
OR (IS_MEMBER('ssis_admin') = 1)
OR (IS_SRVROLEMEMBER('sysadmin') = 1)
OR (1= 1)

The two sets of code will make the view return data differently?
Appreciate your time and input.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Any good documents for the "dynamic queries", that I am not really familiar with.

I suggest these 2 articles by Erland Sommarskog:

The second article uses the 1 = 1 technique and explains the purpose:

The condition WHERE 1 = 1 on line 32 is there so that all other conditions can be added as "AND something" without having to worry about whether there already is a WHERE or not.

Note this technique for developer convenience and not required to use dynamic SQL.

I recommend you first learn T-SQL fundamentals before jumping in to dynamic SQL.






1 Vote 1 ·

Thanks @DanGuzman. Reall appreciate your help and tips. I will work on the studies as you suggested.

0 Votes 0 ·