search to sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

search to sql

Author

Topic

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-18 : 09:52:32

hello forum,
i have this asp form that searches for records depending on user input or selection, these are the form fields:
zip code text box
category drop down box
pro number text box
site number text box
start date text box
design date text box
plus mil radio button
lese mil radio button

none of the fields is required, user can select one or more fields to do the search. currenlty if a user selects a category and site number the application returns all records that contains that category and input site number, but in reality there is only one record in db that contains category and a site number as site number is unique.

this is my query:
Note: i am using session to store the values input in the form

"SELECT zip,category,proNumber,siteNumber,startDate,designDate,totalMil FROM finalTable where zip='" & session("zipSession") & "' or category='" & Session("categorySession") & "' or pronumber='" & Session("pronumberSession") & "' or sitenumber='" & session("sitenumberSession") & "' and startdate>='" & Session("startdateSession") & "' and designdate>='" & Session("designdateSession") & "' and totalMil>='" & Session("totalMilSession") & "' "

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 10:22:33

If you are going to use Dynamic SQL like this:

1. you need to process each parameter to "double up" any embedded single-quotes. Search on SQL Injection for reasons why.

2. You should only include criteria where the user has provided an answer, so something like:

strSQL = "SELECT zip,category,proNumber,siteNumber,startDate,designDate,totalMil FROM finalTable where 1=1 "

if session("zipSession") <> "" then strSQL = strSQL & "AND zip='" & session("zipSession") & "' "

(NOTE: This still needs a single-quote-double-up function)

repeat for each parameter.

Note that you are missing OR and AND in your logic. You need to be careful to parenthesise the OR and AND conditions.

"Would you like a Burger and Fries or Hash Browns?"

is clear to you, but a computer might come back with only Hash Browns! i.e.

"Would you like a (Burger and Fries) or Hash Browns?"

3. You really ought to parameterise your query, but if you are just starting out I would start with the above.

Kristen

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-18 : 12:10:06

Thank you very much Kristen, it works perfect.

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-18 : 15:09:05

one more thing:
i am joining two tables to the the values but now the search is returning total different values, i am missing entries this is how i am joining the tables:

FROM tableFinal JOIN tableOther ON tableFinal.siteNumber = tableOther.NumberOfLocation where 1=1

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 02:05:51

Do you want:

1 = TableFinal ONLY where there are corresponding entries in TableOther?
2 = ALL TableFinal rows and corresponding entries in TableOther IF ANY EXIST?

Your currently have (1)

If you want (2) change the JOIN to LEFT OUTER JOIN

If I've misunderstood the question ask again pls!

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-19 : 10:22:17

quote:
Originally posted by Kristen

3. You really ought to parameterise your query, but if you are just starting out I would start with the above.

if you don't do this, your app is vulnerable to sql injection. a criminal could steal all your data, delete it, whatever they like. may even be able to take control of your server depending on the login permissions this query is executed with.

elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 11:09:17

I did make that as my (1) point

, however if the doubling-up-of-single-quotes is done religiously there is no risk of SQL Injection IMO. But even so parameterised queries would be better

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-19 : 16:02:23

doubling up won't work if the param is supposed to be a non-string type. consider this:

sql = "select foo from bar where myintcol=" + ReplaceQuotes(someStringThatShouldContainAnIntButHackerReplacedItWithMaliciousStuff)

This kind of thing could easily happen for textboxes that are supposed to contain ints but aren't validated.

elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-10-20 : 02:53:51

Sorry, should have said that we quote ALL user-provided parameters, SQL will make an implicit string-to-int conversion.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources