Parameterized query format for select statement

[HowardB]

Hello,

I am connecting to a MS access 2007 Database using the Database connectivity toolkit (DCT).

I need to build a query from some input string controls (Operator, Lot).  Using standard SQL I could use :

SELECT * FROM TEST_SETUP WHERE OPERATOR='string from Operator control' and LOT='string from Lot control';

However this leads to the possibility of an SQL injection attack, so I'd like to use a parameterized query.  I see examples showng the format for an INSERT command, but nothing showing how to call out parameters for a Select statement......

Any ideas?

Thanks!

[marcov09]

Hello Howard,

Thanks for using NI support forums. I’ll suggest building Query string prior to send it. If you want to avoid some characters in order to prevent SQLi then you can implement some comparison logic within the vi to “validate” or “check” the entire string. Functions like concatenate string or string length can be useful on this case.

Have a great day!

.

[HowardB]

Hi Marco,

Thanks for the reply, but not sure how it answers my question.  Yes, I could build a string and send a standard query but what if the user's name is little bobby tables?

[mikeporter]

The answer is in the last frame of you cartoon. Read it again.

If you are letting users type if values that will be used unfiltered, you have a lot bigger problems on your hands than SQL insertion.

Why are you letting operators type in their name? Never let operators type in their own name. In fact don't let users type at all except as a last resort.

Likewise, lot number or id or whatever should not be typed. Selected, yes. Scanned, yes. But not typed.

Mike...

[HowardB]

My point being that there are times when typed input is a requirement.  User name /  password - If you have 5000 users, are you going to scroll through a list?  Yes, there are options such as autocomplete, etc. but often you wouldn't want to expose a list of all users.....

There are reasons the parameterized query was developed, one of them to overcome the potential issues of SQL injection.  Specifically my question concerns the correct format of the Labview parameterized query VI.  I don't want to use unfiltered text input.  That was the reason for the post..... 

[drjdpowell]

---

@HowardB wrote:

I see examples showng the format for an INSERT command, but nothing showing how to call out parameters for a Select statement......

Any ideas?

---

Do it the same as with INSERT commands; use "?" in the SQL for your paramteters.  Does that not work?

[HowardB]

So, something like "SELECT * FROM TEST_DATA WHERE OPERATOR=(operator) and LOT=(lot) values (?,?)"

Does that look correct?  I'll try it tomorrow when I get back to work.....

[dkfire]

More something like this: "SELECT * FROM TEST_DATA WHERE OPERATOR=? and LOT=?"

[drjdpowell]

Ah, you need a good reference on SQL, rather than LabVIEW help.  Look up the SQL syntax for SELECT, and then you use "?" in place of the parameters (as dkfire shows).  

[HowardB]

So for the select statement, the Labview vi does not use named parameters?  So then I must set the parameters in the same order as the ?'s appear?

Thanks for the help!