Select * where 1 = 1

[CJM]

I'm building a search function for one of my applications. The user has the
option to enter a number criteria of criteria, but none are compulsary. I
need to be able to build up a query string that includes only the right
criteria. The simplest way I have found is something like this:

sSQL = "Select field1, field2, etc form table where 1=1"

If Request.Form("Criteria1") <> "" then
sSQL = sSQL & " and criteria1 = " & Request.Form("Criteria1")
End If

If Request.Form("Criteria2") <> "" then
sSQL = sSQL & " and criteria2 = " & Request.Form("Criteria2")
End If


....or something similar.

This will work fine, but I was just wondering if anybody had any
improvements or had opted for a different solution??

Thanks

Chris

 

[Uri Dimant]

CJM
Look at below example written by Erland helps you build the query.



CREATE PROCEDURE search_orders_1 --
1
@orderid int = NULL, --
2
@fromdate datetime = NULL, --
3
@todate datetime = NULL, --
4
@minprice money = NULL, --
5
@maxprice money = NULL, --
6
@custid nchar(5) = NULL, --
7
@custname nvarchar(40) = NULL, --
8
@city nvarchar(15) = NULL, --
9
@region nvarchar(15) = NULL, --
10
@country nvarchar(15) = NULL, --
11
@prodid int = NULL, --
12
@prodname nvarchar(40) = NULL, --
13
@debug bit = 0 AS --
14
--
15
DECLARE @sql nvarchar(4000), --
16
@paramlist nvarchar(4000) --
17
--
18
SELECT @sql = --
19
'SELECT rderID, rderDate, od.UnitPrice, od.Quantity, --
20
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region, --
21
c.PostalCode, c.Country, c.Phone, p.ProductID, --
22
p.ProductName, p.UnitsInStock, p.UnitsOnOrder --
23
FROM Orders o --
24
JOIN [Order Details] od ON rderID = od.OrderID --
25
JOIN Customers c ON o.CustomerID = c.CustomerID --
26
JOIN Products p ON p.ProductID = od.ProductID --
27
WHERE 1 = 1' --
28
--
29
IF @orderid IS NOT NULL --
30
SELECT @sql = @sql + ' AND rderID = @xorderid' + --
31
' AND od.OrderID = @xorderid' --
32
--
33
IF @fromdate IS NOT NULL --
34
SELECT @sql = @sql + ' AND rderDate >= @xfromdate' --
35
--
36
IF @todate IS NOT NULL --
37
SELECT @sql = @sql + ' AND rderDate <= @xtodate' --
38
--
39
IF @minprice IS NOT NULL --
40
SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice' --
41
--
42
IF @maxprice IS NOT NULL --
43
SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice' --
44
--
45
IF @custid IS NOT NULL --
46
SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' + --
47
' AND c.CustomerID = @xcustid' --
48
--
49
IF @custname IS NOT NULL --
50
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%''' --
51
--
52
IF @city IS NOT NULL --
53
SELECT @sql = @sql + ' AND c.City = @xcity' --
54
--
55
IF @region IS NOT NULL --
56
SELECT @sql = @sql + ' AND c.Region = @xregion' --
57
--
58
IF @country IS NOT NULL --
59
SELECT @sql = @sql + ' AND c.Country = @xcountry' --
60
--
61
IF @prodid IS NOT NULL --
62
SELECT @sql = @sql + ' AND od.ProductID = @xprodid' + --
63
' AND p.ProductID = @xprodid' --
64
--
65
IF @prodname IS NOT NULL --
66
SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' --
67
--
68
SELECT @sql = @sql + ' ORDER BY rderID' --
69
--
70
IF @debug = 1 --
71
PRINT @sql --
72
--
73
SELECT @paramlist = '@xorderid int, --
74
@xfromdate datetime, --
75
@xtodate datetime, --
76
@xminprice money, --
77
@xmaxprice money, --
78
@xcustid nchar(5), --
79
@xcustname nvarchar(40), --
80
@xcity nvarchar(15), --
81
@xregion nvarchar(15), --
82
@xcountry nvarchar(15), --
83
@xprodid int, --
84
@xprodname nvarchar(40)' --
85
--
86
EXEC sp_executesql @sql, @paramlist, --
87
@orderid, @fromdate, @todate, @minprice, @maxprice, --
88
@custid, @custname, @city, @region, @country, --
89
@prodid, @prodname --
90

 

[Alejandro Mesa]

Have you heard about sql injection?

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html



AMB

 

[CJM]

CJM
Look at below example written by Erland helps you build the query.

[snip]


Uri,

I understand your solution, and I can see some advantages and some
disadvantages. But rather than pre-empt you, could you explain the benefits
of using this method?

Thanks

Chris

 

[Uri Dimant]

Hi
Let's start with why calling stored procedure is better that sending adhoc
query thru the network. I think I don't need to explain, right?
Read about sql server injection that Alejandro mentioned because it is very
important issue.

An execution plan which created by stored procedure can be reused which will
be reduced a server overhead
Stored procedures can encapsulate logic. You can change stored procedure
code without affecting clients
Network traffic is reduced significantly.

CJM
Look at below example written by Erland helps you build the query.

[snip]


Uri,

I understand your solution, and I can see some advantages and some
disadvantages. But rather than pre-empt you, could you explain the benefits
of using this method?

Thanks

Chris

 

[CJM]

Have you heard about sql injection?

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

I have - which is why I filter my form data.

The code snippet I posted was off-the-cuff, just to demonstrate a concept.
It is not my live code.

 

[Alejandro Mesa]

Good then. Hope the link posted can help you.



AMB

 

[Bob Barrows [MVP]]

I have - which is why I filter my form data.

If you think filtering form data will prevent sql injection, then you are
either uninformed or naive. These links illustrate several techniques to
defeat filters:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

The only way to prevent sql injection is to utilize parameters. Using
dynamic sql is an invitation to be hacked.

Bob Barrows

 

[CJM]

If you think filtering form data will prevent sql injection, then you are
either uninformed or naive.

Probably a bit of both, not that it matters here.

These links illustrate several techniques to

defeat filters:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

The only way to prevent sql injection is to utilize parameters. Using
dynamic sql is an invitation to be hacked.

Bob Barrows

Point taken [to an extent].

However, should a hacker be in a position to even use this system it means
that our physical security cordon has been breached, and we are up the
proverbial creek. Once in this position, he has much bigger and easier fish
to fry.

Filtering is more than good enough for our purposes. However, I concede that
parameterized queries are even more effective.

Chris

 

[Bob Barrows [MVP]]

If you think filtering form data will prevent sql injection, then
you are either uninformed or naive.

Probably a bit of both, not that it matters here.

These links illustrate several techniques to

defeat filters:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

The only way to prevent sql injection is to utilize parameters. Using
dynamic sql is an invitation to be hacked.

Bob Barrows

Point taken [to an extent].

However, should a hacker be in a position to even use this system it
means that our physical security cordon has been breached,

Why do you say that? These articles show several techniques a hacker can use
to discover dynamic sql is being used without having access to your asp
files (which is what I assume you mean by "physical security" being
breached.

Having said that, my main reasons for avoiding dynamic sql are:
1. performance
2. ease of coding

Bob Barrows

 

[CJM]

Why do you say that? These articles show several techniques a hacker can
use
to discover dynamic sql is being used without having access to your asp
files (which is what I assume you mean by "physical security" being
breached.

Unless said hacker is Houdini, he needs physical access to one of our
buildings in order access our PC's. He then needs to gain access to the
network. Only then can he start trying his SQL injection. If he is on the
inside already, then he can already do a lot of damage elsewhere.

And if I recall correctly, wasn't it you that has argued with Aaron on the
issue of performance? Using parameterized queries means using the Command
object, which is a bit more cumbersome than using just a Connection. In most
cases, I simply use Connection.Execute...

Ease of coding? More code, and more complicated at that. However, if I were
sufficiently motivated, I wouldnt let that stop me using a particular
technique.

Chris

 

[Bob Barrows [MVP]]

Unless said hacker is Houdini, he needs physical access to one of our
buildings in order access our PC's. He then needs to gain access to
the network. Only then can he start trying his SQL injection. If he
is on the inside already, then he can already do a lot of damage
elsewhere.

And if I recall correctly, wasn't it you that has argued with Aaron
on the issue of performance? Using parameterized queries means using
the Command object, which is a bit more cumbersome than using just a
Connection. In most cases, I simply use Connection.Execute...

Ease of coding? More code, and more complicated at that. However, if
I were sufficiently motivated, I wouldnt let that stop me using a
particular technique.

Chris

As I've said time and time again, a Command object is not needed to pass
parameters to a stored procedure:
http://groups-beta.google.com/group...sp.db/msg/563f6ac9b41090f9?hl=en&lr=&c2coff=1

You don't even need to use a stored procedure to utilize parameters, but
then you do need a Command object to make that work.

Bob Barrows

 

[Alan Barker]

I made use of a similar concept (if not a little more complex) for a
previous project.

The problems I found are when users want to query OR instead/aswell as AND,
use substring matching, work with Boolean TRUE of FALSE (usually users are
more amenable to yes or no) or query dates etc, all within the same query.
If you can restrict your input to 1 or more integer inclusive values, work
with a single table with simple data types you're golden. Then again...if
you can stick all your users on a SQL course and give them Query Analyser
your going home early!

I did remove the 1=1 'hack' even though it made the code a little more
structured.

Al.
actually..... thinking about it......no.... users with Query Analyser is a
bad idea!

 

[Dave Anderson]

The only way to prevent sql injection is to utilize parameters. Using
dynamic sql is an invitation to be hacked.

It should be noted that parameters are not sufficient if the stored
procedure executes a string. I should know -- I have spent plenty of time
remediating a vendor app that favors building and executing dynamic strings
inside SPs.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Dave Anderson]

And if I recall correctly, wasn't it you that has argued with Aaron
on the issue of performance? Using parameterized queries means using
the Command object, which is a bit more cumbersome than using just a
Connection. In most cases, I simply use Connection.Execute...

That was probably me. And I only advocate the Command object for INSERTs and
UPDATEs, favoring its structure and verbosity over performance for those
crucial tasks. I consider CN.Execute a very reasonable alternative much of
the time, and use it unflinchingly for lookups.


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Bob Barrows [MVP]]

It should be noted that parameters are not sufficient if the stored
procedure executes a string. I should know -- I have spent plenty of
time remediating a vendor app that favors building and executing
dynamic strings inside SPs.

Yes. Any time dynamic sql is used. In stored procedures, the sp_executeSQL
system procedure can be used to parameterize some dynamic sql statements in
order to avoid concatenation.

Bob Barrows

 

[CJM]

As I've said time and time again, a Command object is not needed to pass
parameters to a stored procedure:
http://groups-beta.google.com/group...sp.db/msg/563f6ac9b41090f9?hl=en&lr=&c2coff=1

You don't even need to use a stored procedure to utilize parameters, but
then you do need a Command object to make that work.

Bob Barrows

For short-term practical reasons, I'm sticking with my current solution.
However, I'll be embarking on stage 2 in 6 weeks or so, so I'll make some
changes then. I quite like the sp-as-a-connection-method alternative.

Cheers

Chris

 

[Bob Barrows [MVP]]

I was reading thru MSDN last week

http://msdn.microsoft.com/library/default.asp?url=/library/en
us/vbaac11/ado210/htm/mdobjconnection.asp

& saw this about stored procedures in MDAC 2.6 and higher: we can
use a connection object to run an SP w/ parameters, this way:

dim cn as ADO.connection
' ... set up cn ...

' run SP usp_AddSales (@prod_id int, @qty tinyint, @sales_date
datetime) cn.usp_AddSales "255, 1, '20041025'"

No, you misread it. The statement should look like this (no double-quotes,
except around a string argument if one exists. Also, the date should be
passed as an explicit date, not a string):

cn.usp_AddSales 255, 1, #2004-10-25#

The SP's parameters are in the double-quoted string after the SP's
name.

Again, this will fail. The arguments should be passed as if the stored
procedure was a native method of the connection object.

To set the SP's result to a recordset just put a ref to an
ADO.Recordset after the parameters:

correct, except the idea you got about delimiting all the arguments within a
single pair of double-quotes.

dim rs as ADO.Recordset
' ... set up rs ...

cn.usp_AddSales "255, 1, '20041025'", rs

No. Like this:

cn.usp_AddSales 255, 1, #2004-10-25#, rs

Bob Barrows