Link to home
Create AccountLog in
Avatar of Wildone63
Wildone63

asked on

Help Building Dynamic Where Clause

I am using VB.net VS-2008.

I have two aspx pages, the first page has a text box and a submit button. I use this to pass a session variable to the second page.

I want to add this session variable to the where clause of a sql query.
I can not get the query to show the value of the session variable...

Here is what I am trying
I am populating the session variable manually here.

        Session("owner") = 2519
        Dim owner As String = Session("owner")
        Dim myquery As String = "SELECT Administrator.ITEMS.FULL_NAME, Administrator.ITEMS.SALESDESCRIPTION FROM Administrator.ITEMS WHERE Administrator.ITEMS.PART_BELONGS_TO_ID = @owner"
        Dim mycs As String = "Dsn=MyNet.com;uid=user;pwd=password"
        MsgBox(owner)
        MsgBox(myquery)

The msgbox for owner displays the value of the variable owner but them msgbox for myquery show @owner instead of the value.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of iHadi
iHadi
Flag of Syrian Arab Republic image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Wildone63
Wildone63

ASKER

Thank You. Your comment was not wrong but The answer to my question is..to build the complete string outside of the call to the database. So it does not matter which db because you can build the string to match the db requirements.

So the string I needed was built using the below by adding the var to the text with the + sign. then I could add more variables and or text by seperating them by the "" marks.

"SELECT Administrator.ITEMS.FULL_NAME, Administrator.ITEMS.SALESDESCRIPTION FROM Administrator.ITEMS WHERE Administrator.ITEMS.PART_BELONGS_TO_ID = " + myvar

Thank You, you did point me in the correct direction by making me realize passing the vars the way I was trying was db specific. or DB Driver specific... so build the string "VB" style then pass the whole string to the db.
well, the above approach you are using has some security issues.

"SELECT Administrator.ITEMS.FULL_NAME, Administrator.ITEMS.SALESDESCRIPTION FROM Administrator.ITEMS WHERE Administrator.ITEMS.PART_BELONGS_TO_ID = " + myvar

this type of query building is vulnerable to sql injection. for example if myvar was set to the following:

myvar = "1; drop table ITEMS;"

If this sql is executed, the ITEMS table will be simply deleted !!! This will not be an issue if you pass myvar as a parameter, and the db system will consider it's value as a value string and not part of the sql command.

Another issue for this type of query building is db type compatibility. If myvar was a string, you need to add it between two [ ' ] characters, and if it was a date, you need to format it as YYYY-MM-DD for mysql, or add it between two [ # ] characters for access, ...

Also if myvar="text1 ' text2", this will cause a syntax error in you sql and you will need to escape the ' character. In other words, you need to escape all special sql characters manually.

The other approach does not need any of this, just pass the sql string and the parameters array in their original type and let the db system do the rest.
Thank You. I do realize all that you said, but the limitations of the odbc driver I am forced to use won't allow me to pass parameters