February 1, 2005 at 3:42 am
Hi all, can someone help me with the following scenario.
I have an address table – tblAddress with the following structure…
IntAddressID
StrAddressLine1
StrAddressLine2
StrAddressLine3
StrAddressLine4
StrAddressLine5
StrPostcode
I want to allow the user to pass in criteria to search and return all matching full addresses.
The user will pass three parameters as follows: -
1. The column name to search which could be any of the address lines or the postcode or search all of the address (in which case Full Address would be passed in).
2. The operator… =, Not =, Like or Not Like.
3. The search value eg 55 high street or eet (which would get everything with street).
Can I code this as one statement perhaps with some sort of dynamic SQL or do I need to use a nested if statement.
Any help much appreciated
Thank you.
CCB
February 1, 2005 at 6:55 am
What front end are you using? I once built an entire query front end for users in VB where all the logic was in VB, not SQL. I simply built an SQL statement based on values from a form and executed the statement through ADO.
Ryan
February 1, 2005 at 7:11 am
Dynamic sql (as well as Ryans solution) is vunerable to sql injection. Many posts on this site cover this. If this is acceptable then building the query in or out of sql is possible.
If you want to use procedures then you will have 28 possible combinations of column and operator which is a lot of if's in one procedure. You could create 7 procs (one for each column) and proc test for the 4 operators.
Otherwise create 28 specifically named procs and construct the proc name at the client and pass only the search value.
Far away is close at hand in the images of elsewhere.
Anon.
February 1, 2005 at 8:16 am
OK, I have reduced the number of operators to 2... = and like and used the following code. Any comments on the effectiveness of it - it does what I want, but not sure about if it is very economocal in terms of resources.
CCB.
CREATE PROCEDURE [dbo].[prc273_AT_incidents_find_addresses]
(@strSearched varchar(50),
@strOperator varchar(50),
@strValue varchar(50))
AS
Select A.strAddressLine1,
A.strAddressLine2,
A.strAddressLine3,
A.strAddressLine4,
A.strAddressLine5,
A.strPostcode,
A.intXcoordinate,
A.intYcoordinate,
SubString(A.strRemarks, 1, 1) as strRemarks,
0 as color,
0 as error,
0 as selected,
0 as found
From tblMapAddress A
Where Case @strOperator
When '=' Then
Case @strSearched
When 'Full Address' Then
Case When A.strAddressLine1 = @strValue or A.strAddressLine2 = @strValue or A.strAddressLine3 = @strValue or A.strAddressLine4 = @strValue or A.strAddressLine5 = @strValue or A.strPostcode = @strValue Then 1 End
When 'Address Line 1' Then
Case When A.strAddressLine1 = @strValue Then 1 End
When 'Address Line 2' Then
Case When A.strAddressLine2 = @strValue Then 1 End
When 'Address Line 3' Then
Case When A.strAddressLine3 = @strValue Then 1 End
When 'Address Line 4' Then
Case When A.strAddressLine4 = @strValue Then 1 End
When 'Address Line 5' Then
Case When A.strAddressLine5 = @strValue Then 1 End
When 'Postcode' Then
Case When A.strPostcode = @strValue Then 1 End
End
When 'Like' Then
Case @strSearched
When 'Full Address' Then
Case When A.strAddressLine1 Like @strValue or A.strAddressLine2 Like @strValue or A.strAddressLine3 Like @strValue or A.strAddressLine4 Like @strValue or A.strAddressLine5 Like @strValue or A.strPostcode Like @strValue Then 1 End
When 'Address Line 1' Then
Case When A.strAddressLine1 Like @strValue Then 1 End
When 'Address Line 2' Then
Case When A.strAddressLine2 Like @strValue Then 1 End
When 'Address Line 3' Then
Case When A.strAddressLine3 Like @strValue Then 1 End
When 'Address Line 4' Then
Case When A.strAddressLine4 Like @strValue Then 1 End
When 'Address Line 5' Then
Case When A.strAddressLine5 Like @strValue Then 1 End
When 'Postcode' Then
Case When A.strPostcode Like @strValue Then 1 End
End
End = 1
Order by A.strAddressLine1,
A.strAddressLine2,
A.strAddressLine3
February 1, 2005 at 8:31 am
David, how is my solution volnuerable? The VB application is locked down so that almost all values (and all operators) are drop downs of some kind and not editable. The statement cannot be changed from "SELECT..." and the app is compiled so ADO cannot be changed.
Can you provide some additional info - or links to reading I should be doing?
Thanks
Ryan
February 1, 2005 at 8:40 am
Hi All,
Dynamic SQL has vulnerabilities - mainly based around web front ends. Just search for SQL Injection in Dynamic SQL. The risk is negligable if you are working on an internal system and there are configuration settings that can remove the risk, even with web front ends. You just need to be aware of the risks.
The problem isn't really relevant to an internal VB app. And there are loads of postings around the pro's and con's of Dynamic SQL - just check a thread or two and you will get the general idea.
Charlotte - If you are doing this purely SQL based, without VB - Dynamic SQL should reduce the amount of code you need to write, this should help you on your merry way:-
DECLARE @FieldToSearchOn nvarchar(100),
@Criteria nvarchar(100),
@SearchTerm nvarchar(100),
@SQL nvarchar(4000)
SET @FieldToSearchOn = 'StrAddressLine1'
SET @Criteria = 'LIKE'
SET @SearchTerm = '746'
SET @sql = 'SELECT IntAddressID, StrAddressLine1, StrAddressLine2, StrAddressLine3,
StrAddressLine4, StrAddressLine5, StrPostcode FROM tblAddress
WHERE ' + @FieldToSearchOn + ' ' + @Criteria + ' ' + @SearchTerm
EXEC (@SQL)
PRINT @sql
Steve
We need men who can dream of things that never were.
February 1, 2005 at 8:51 am
Ryan,
The vunerability is not definate but in the variability of what is passed. If there is no possibilty of free text then the vunerability is minimal however any single field of free text makes the query vunerable.
For instance if I have a query in a procedure
SELECT col1 FROM
WHERE col2 = @sometext
then whatever is passed is matched regardless of content (single quotes will cause an error).
However if I create the query in an app to run against a connection
sql = "SELECT col1 FROM
WHERE col2 = '" & txtQuery & "'"
conn.execute(sql)
Then if I typed the following in txtQuery
' DELETE * FROM
SELECT '
Guess what happens?
Far away is close at hand in the images of elsewhere.
Anon.
February 1, 2005 at 8:56 am
Ah yes, I see exactly what you mean. Thanks for the illustration. RH
February 1, 2005 at 9:20 am
Forgot to mention - if you are doing Dynamic SQL within a stored procedure, when you EXEC the sql string, it generates a new SPID of its own. The sql (and the results) are only live for this line only.
If you need to return the results to use later in your sproc or for any other reasons you need to still create the dynamic sql string in the same way but use sp_executesql with OUTPUT variables, instead of just EXEC.
E.g:-
EXEC sp_executesql @sql, N'@Exists varchar(50) OUTPUT', @Exists OUTPUT
If you have a seach in these forums you will find there are some excellent articles on sp_executesql.
Hope this all helps
Have fun
Steve
We need men who can dream of things that never were.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply