Dynamically build sql string from GET result

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

Development Tools

Other Development Tools

Dynamically build sql string from GET result

Author

Topic

glafontaine
Starting Member

5 Posts

Posted - 2007-03-23 : 14:55:26

Hi,
I’m working with a web page that contains ASP, SQL and VB, I’m trying to build my SQL query depending on the GET result. I have tested my Inner Join (myquery) and works great but as soon as I try to add to my where conditions from my GET results nothing works. I have 7 variables coming in from my GET and if empty SQL should pull all results from that field. Can anyone help? Am I going about this all wrong? Am I describing my problem correctly?

<%
Myquery = "SELECT Table1.*, Table2.*, Table3.*, Table4.*, Table5. Field4, Table6.*, Table7.*, Table8.* FROM ((((((Table1 INNER JOIN Table2 ON Table1.Id_Field1 = Table2.Id_Field1) INNER JOIN Table3 ON Table1.Id_Table3 = Table3.Id_Table3) INNER JOIN Table4 ON Table1.Id_Table4 = Table4.Id_Table4) INNER JOIN Table5 ON Table1.Id_Table5 = Table5.Id_Table5) INNER JOIN Table6 ON Table1.Id_Field2 = Table6.Id_Field2) INNER JOIN Table7 ON Table1.Id_Field3 = Table7.Id_Field3) INNER JOIN Table8 ON Table1.Id_Table8 = Table8.Id_Table8 WHERE 1=1"

Dim Table1__MMColParam1
If (Request.QueryString("Id_Table3") <> "") Then
Table1__MMColParam1 = Request.QueryString("Id_Table3")
Myquery += " AND Table1.Id_Table3 = " + Replace(Table1__MMColParam1, "'", "''")""
End If

Dim Table1__MMColParam2
If (Request.QueryString("Id_Table1") <> "") Then
Table1__MMColParam2 = Request.QueryString("Id_Table1")
Myquery += " AND Table1.Id_Table1 = " + Replace(Table1__MMColParam2, "'", "''")""
End If

Dim Table1__MMColParam3
If (Request.QueryString("Id_Field2") <> "") Then
Table1__MMColParam3 = Request.QueryString("Id_Field2")
Myquery += " AND Table1.Id_Field2 = " + Replace(Table1__MMColParam3, "'", "''")""
End If

Dim Table1__MMColParam4
If (Request.QueryString("Id_Table8") <> "") Then
Table1__MMColParam4 = Request.QueryString("Id_Table8")
Myquery += " AND Table1.Id_Table8 = " + Replace(Table1__MMColParam4, "'", "''")""
End If

Dim Table1__MMColParam5
If (Request.QueryString("Id_Field3") <> "") Then
Table1__MMColParam5 = Request.QueryString("Id_Field3")
Myquery += " AND Table1.Id_Field3 = " + Replace(Table1__MMColParam5, "'", "''")""
End If

Dim Table1__MMColParam6
Table1__MMColParam6 = ""
If (Request.QueryString("Field5") <> "") Then
Table1__MMColParam6 = Request.QueryString("Field5")
End If

Dim details
Dim details _numRows

Set details = Server.CreateObject("ADODB.Recordset")
details.ActiveConnection = MM_db1_STRING
details.Source = Myquery
details.CursorType = 0
details.CursorLocation = 2
details.LockType = 1
details.Open()

details _numRows = 0
%>

Thank you,

GG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-23 : 17:29:50

The += operator doesn't work in VB Script, only in VB .NET, so replace
Myquery += " AND Table1.Id....
with
Myquery = Myquery + " AND Table1.Id....

And DON'T DO IT THIS WAY!
You need to declare parameters in your SQL statement and then populate them with the values from the form inputs. If you write your code this way it will be vulnerable to a serious security problem called SQL Injection. See
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-23 : 19:14:51

To follow up on what snSQL wrote, not only is it safer to use parameters, it is easier and it follows best practices. So, there is never a reason to do things the way you have -- you are making work harder on yourself.

See here for more info:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

the examples apply to VB.NET, but the concepts are exactly the same for ASP/VBScript, just different syntax.

- Jeff
http://weblogs.sqlteam.com/JeffS

glafontaine
Starting Member

5 Posts

Posted - 2007-03-29 : 11:48:03

Thank you for your help and the advice! Great forum.

GG

glafontaine
Starting Member

5 Posts

Posted - 2007-04-26 : 10:02:55

Hi,
This forum has been helpful in the past so I’ll give this another try. From my last posting I went back to the books and learn how to program Sql injection proof by parameter. I’m almost done and my sql is dynamically built successfully. Now my issue is that I getting multiple variables with same name from checkboxes submitted from a different page. The variables are past by URL as:

Ex. Id=1&Id=2&Id=3

I’m doing my GET like this:

If (Request.QueryString("Id") <> "") Then
For counter = 1 To Request.QueryString ("Id").count
Tab__MMColParam1 = Request.QueryString("Id")(counter)
Tab_cmd.CommandText = Tab_cmd.CommandText + " AND Table.Id LIKE ?"
Tab_cmd.Parameters.Append Tab_cmd.CreateParameter("param1", 5, 1, -1,Tab__MMColParam1) ' adDouble
End If

This only works for one value, one checkbox is checked, when multiple checkboxes are checked only the last value(Id=3) is processed. From what I understood I need to increment my “Tab __MMColParam1” and “param1” in the loop but my attempt failed:

If (Request.QueryString("Id") <> "") Then
i=0
For counter = 1 To Request.QueryString ("Id").count
i=i+1
Tab__MMColParam(i) = Request.QueryString("Id")(counter)
Tab_cmd.CommandText = Tab_cmd.CommandText + " AND Table.Id LIKE ?"
Tab_cmd.Parameters.Append Tab_cmd.CreateParameter("param(i)", 5, 1, -1, Tab__MMColParam(i)) ' adDouble
End If

Thank you

GG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources