CODEWITHSUNDEEP
sql
Cole
Cole

Reputation: 11

Basic SQL statement returning error

I am new to SQL and I keep getting an error with the following SQL statement: 

SELECT Id,Name
FROM {PositionInCompany}
WHERE @AdvancedFilter

At the moment I am just testing the default case for AdvancedFilter, so it is set to"1=1" and should return all the names and ids from PositionInCompany. This works if I simply put "1=1" in, but not if I use the variable (which has exactly the same string value).

The error is attached. What am I missing?

Cole

enter image description here

Upvotes: 0

Views: 600

Answers (3)

Cole
Cole

Reputation: 11

Thank you for your responses.

I'm doing this in Outsystems, but I assumed it was a simple SQL problem. However, there was simply an "Expand Inline" option that needed to be selected in order to insert a string statement. It works fine now.

regards, Cole

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Parameters in SQL are placeholders for data. If they contain SQL code, that code is ignored by the database and is treated as data.
This is why you can't send SQL chunks as parameter, and that's also the reason why you can't parameterize identifiers.

If you need a dynamic where clause, you need to use dynamic SQL, but that usually have a cost in both performance and security.

However, I suspect your current SQL is wrong in the first place, assuming FROM {PositionInCompany} means you are concatenating the table name into the sql string (only assuming, since you didn't provide any information to show that).

If my assumption is correct, you need to stop doing that, read about SQL injection and how parameterized queries protects you from it, and understand that this form of concatenation is also vulnerable to SQL injection attacks.

In SQL, don't look for shortcuts. Write specific queries for specific tables. That's the only safe way to do it, and 99.9% of the time it has the best performance.

Upvotes: 1

Joost K
Joost K

Reputation: 1116

This is probably because as you said it is a string which will translate to :

SELECT Id,Name FROM {PositionInCompany} WHERE '1=1'

sql would read this as just a string and it wont know it is something it can excute. You could change the value of AdvancedFilter to something else

@AdvancedFilter = 'test'
SELECT Id,Name FROM {PositionInCompany} WHERE 'test'=@AdvancedFilter

or use something to dynamically build the query

Upvotes: 1

Related Questions