4 Replies - 5220 Views - Last Post: 28 October 2013 - 11:14 AM Rate Topic: -----

#1 OzChris   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 26-October 13

Using variables to build an SQL select command

Posted 26 October 2013 - 07:39 AM

This is my first post so hope I get it right, and hoping someone can help me

The part of my application I am having a problem with is a mail merge function that replaces pre-defined text in a string with data from a SQL Database. This function is used for sending out personalised emails, account information, customizing data on a web page etc.

I want to take text from a pre-defined template.
I.E. DataStr = “Thank you [FirstName] for using our service.”

Step one:
Get the users first name from the database by using information stored in the MailMerge Table of database.
In the MailMerge table there are two fields MergeCode and MergeData
MergeCode has the code to match the string in the replace function [FirstName]. The MergeData stores a comer delimited string to find the correct piece of data in the database.

MergeData = DBTable,RetrieveField,KeyField,MatchString
It looks like this “Users,FirstName,ID,UserID

I then split the data from the MergeData into an array called TableStr = Split(DR("MergeData"), ";", -1, CompareMethod.Text)
This gives me an array with 5 elements
TableStr(0) = “Users”
TableStr(1) = “FirstName”
TableStr(2) = “ID”
TableStr(3) = “UserID”

So the Database Select Command is built like this.
ReturnStr = "Select " & TableStr(2) & "," & TableStr(1) & " from " & TableStr(0) & " where " & TableStr(2) & "=" & TableStr(3))

Here is the problem. I have a global variable UserID that is populated with a User Number when the user logs on, for this scenario let’s make that UserID = 100

So I want the SQL string to be “Select UserID,FirstName from Users where ID=” 100

But it comes out as “Select UserID,FirstName from Users where ID=” UserID
I am sure there is a simple answer for this but I just can’t seem to find it.

Other Information
I am using Visual Studio 2010 Ultimate
Building a web site asp.net using vb.net

Thanks in advance
Chris

Is This A Good Question/Topic? 0
  • +

Replies To: Using variables to build an SQL select command

#2 andrewsw   User is online

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,509
  • Joined: 12-December 12

Re: Using variables to build an SQL select command

Posted 26 October 2013 - 07:45 AM

ReturnStr = "Select " & TableStr(2) & "," & TableStr(1) & " from " & TableStr(0) & " where " & TableStr(2) & "=" & TableStr(3)

(removed the extra bracket)

Well TableStr(3) contains the word UserID so, of course, that is how it comes out. If you want to use the number then replace TableStr(3) in this expression with the variable that contains the value of 100.

This post has been edited by andrewsw: 26 October 2013 - 07:47 AM

Was This Post Helpful? 0
  • +
  • -

#3 OzChris   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 26-October 13

Re: Using variables to build an SQL select command

Posted 26 October 2013 - 07:54 AM

View Postandrewsw, on 26 October 2013 - 07:45 AM, said:

ReturnStr = "Select " & TableStr(2) & "," & TableStr(1) & " from " & TableStr(0) & " where " & TableStr(2) & "=" & TableStr(3))

Well TableStr(3) contains the word UserID so, of course, that is how it comes out. If you want to use the number then replace TableStr(3) in this expression with the variable that contains the value of 100.



Wow thanks for the really quick reply, I am very impressed.
Thanks for your time Andrew but the problme is the UsserId will be diffrent for each user that logs in, also I want to be able to intregate other tables in the database so UserID may become DocNum, or any other variable, that was wqhy I wanted to build a function that can accpet any string of

View Postandrewsw, on 26 October 2013 - 07:45 AM, said:

ReturnStr = "Select " & TableStr(2) & "," & TableStr(1) & " from " & TableStr(0) & " where " & TableStr(2) & "=" & TableStr(3))

Well TableStr(3) contains the word UserID so, of course, that is how it comes out. If you want to use the number then replace TableStr(3) in this expression with the variable that contains the value of 100.



Wow thanks for the really quick reply, I am very impressed.
Thanks for your time Andrew but the problme is the UsserId will be diffrent for each user that logs in, also I want to be able to intregate other tables in the database so UserID may become DocNum, or any other variable, that was wqhy I wanted to build a function that can accpet any string of

[quote name='OzChris' date='26 October 2013 - 07:53 AM' timestamp='1382799215' post='1923195']

View Postandrewsw, on 26 October 2013 - 07:45 AM, said:

ReturnStr = "Select " & TableStr(2) & "," & TableStr(1) & " from " & TableStr(0) & " where " & TableStr(2) & "=" & TableStr(3))

Well TableStr(3) contains the word UserID so, of course, that is how it comes out. If you want to use the number then replace TableStr(3) in this expression with the variable that contains the value of 100.



Wow thanks for the really quick reply, I am very impressed.
Thanks for your time Andrew but the problem is the UsserId will be different for each user that logs in, also I want to be able to interrogate other tables in the database so UserID may become DocNum, or any other variable, that I already have a reference to in the application. That’s was why I wanted to build a function that can accept any string that matches a field in the database.
I guess what I need to do is just get the value of the variable UserID and that’s my real problem.
Thanks again Andrew for your quick answer, any more thoughts
Was This Post Helpful? 0
  • +
  • -

#4 h4nnib4l   User is online

  • The Noid
  • member icon

Reputation: 1350
  • View blog
  • Posts: 1,927
  • Joined: 24-August 11

Re: Using variables to build an SQL select command

Posted 28 October 2013 - 10:40 AM

How much time are you really saving by creating a method that dynamically creates a SELECT statement via passed variables? What if you need to select 3 values from a table? Writing out simply queries doesn't take that long, and the more string concatenation you use, the more holes you're opening to SQL Injection. Here's a great tutorial on parameterizing SQL queries by Curtis Rutland. One thing to note: you can't parameterize the table name in a query, and that's generally a good thing. There are always exceptions, but it's usually safe to assume that one is not the exception to a rule.
Was This Post Helpful? 1
  • +
  • -

#5 andrewsw   User is online

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,509
  • Joined: 12-December 12

Re: Using variables to build an SQL select command

Posted 28 October 2013 - 11:14 AM

Assuming there are only a specific list of table-names to choose from then I would prefer to construct this part of the sql-statements using a Select..Case statement. Something like:

Select Case tableChoice
    Case 1
        sSql = "SELECT field1, field2 FROM table1 "
    Case 2
        sSql = "SELECT field1, field2 FROM table2 "
    Case Else
        'Doh!! Must be a mistake!
End Select

This reduces to a minimum the injecting of variables into the sql; the user is restricted to making a valid choice, and they do not supply values that are injected directly. The Case Else is important to ignore any invalid selection by the user.

Ideally, you reduce the injection as far as possible to just criteria, but then don't inject them anyway: use parameterized queries.

If there are a large number of table and field-name combinations I would still consider storing these sql-part-strings in a table, rather than constructing them dynamically. I appreciate that this approach is not always feasible.

This post has been edited by andrewsw: 28 October 2013 - 11:16 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1