By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,089 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Select * where 1 = 1

P: n/a
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
Jul 22 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
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 o.OrderID, o.OrderDate, 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 o.OrderID = 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 o.OrderID = @xorderid' + --
31
' AND od.OrderID = @xorderid' --
32
--
33
IF @fromdate IS NOT NULL --
34
SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate' --
35
--
36
IF @todate IS NOT NULL --
37
SELECT @sql = @sql + ' AND o.OrderDate <= @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 o.OrderID' --
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

"CJM" <cj*******@newsgroups.nospam> wrote in message
news:u$**************@TK2MSFTNGP09.phx.gbl...
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

Jul 22 '05 #2

P: n/a
Have you heard about sql injection?

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

AMB

"CJM" wrote:
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

Jul 22 '05 #3

P: n/a
CJM

"Uri Dimant" <ur**@iscar.co.il> wrote in message
news:uj*************@TK2MSFTNGP12.phx.gbl...
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
Jul 22 '05 #4

P: n/a
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" <cj*******@newsgroups.nospam> wrote in message
news:On*************@TK2MSFTNGP09.phx.gbl...

"Uri Dimant" <ur**@iscar.co.il> wrote in message
news:uj*************@TK2MSFTNGP12.phx.gbl...
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

Jul 22 '05 #5

P: n/a
CJM

"Alejandro Mesa" <Al***********@discussions.microsoft.com> wrote in message
news:09**********************************@microsof t.com...
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.
Jul 22 '05 #6

P: n/a
Good then. Hope the link posted can help you.

AMB

"CJM" wrote:

"Alejandro Mesa" <Al***********@discussions.microsoft.com> wrote in message
news:09**********************************@microsof t.com...
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.

Jul 22 '05 #7

P: n/a
CJM wrote:
"Alejandro Mesa" <Al***********@discussions.microsoft.com> wrote in
message news:09**********************************@microsof t.com...
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.

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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._injection.pdf
http://www.spidynamics.com/papers/SQ...WhitePaper.pdf

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

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #8

P: n/a
CJM

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uU**************@TK2MSFTNGP09.phx.gbl...
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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._injection.pdf
http://www.spidynamics.com/papers/SQ...WhitePaper.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
Jul 22 '05 #9

P: n/a
CJM wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uU**************@TK2MSFTNGP09.phx.gbl...
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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._injection.pdf
http://www.spidynamics.com/papers/SQ...WhitePaper.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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #10

P: n/a
CJM

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:OI**************@TK2MSFTNGP11.phx.gbl...
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
Jul 22 '05 #11

P: n/a
CJM wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:OI**************@TK2MSFTNGP11.phx.gbl...
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

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/...n&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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #12

P: n/a
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!

"CJM" <cj*******@newsgroups.nospam> wrote in message
news:u$**************@TK2MSFTNGP09.phx.gbl...
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

Jul 22 '05 #13

P: n/a
Bob Barrows [MVP] wrote:
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.
Jul 22 '05 #14

P: n/a
CJM wrote:
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.
Jul 22 '05 #15

P: n/a
Dave Anderson wrote:
Bob Barrows [MVP] wrote:
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.


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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #16

P: n/a
CJM

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ov**************@TK2MSFTNGP15.phx.gbl...
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/...n&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
Jul 22 '05 #17

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I was reading thru MSDN last week

http://msdn.microsoft.com/library/de...rl=/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'"

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

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

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

cn.usp_AddSales "255, 1, '20041025'", rs
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQdtFD4echKqOuFEgEQKiaACfVEWnmJTqZZBcJN/wBeuHSQARMzUAoJUf
KT5ZEqts6gRJPRBlhQp+fr6F
=VHp6
-----END PGP SIGNATURE-----
CJM wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ov**************@TK2MSFTNGP15.phx.gbl...
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/...n&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.

Jul 22 '05 #18

P: n/a
MGFoster wrote:

I was reading thru MSDN last week

http://msdn.microsoft.com/library/de...rl=/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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.