VB.net - OleDB - How to use parametrized table names in a SQL Command?

Hi,

I was quite displeased to learn that I can't parameterize tablenames/fieldnames in an OledbCommand....

Code:

---

        Dim CMDStr As String = "SELECT CustomerID FROM ?;"  'OleDB use a question mark as the placeholder?
        Dim CMD As New OleDbCommand(CMDStr, vConnection)
        CMD.Parameters.AddWithValue("@Tablename", Tablename)
        Return CMD.ExecuteScalar 'fail...............

---

Use parametrized queries they say! SQL injection you will fall victim to, they say!

I suppose that leaves string concatenation as my only option to solve this problem? If that is true, any tips on validating user input? Any libraries/tools built-in to .NET that help with this, or is it as simple as using a white/black list to check for bad expressions / keywords?

For context: Scope of the application is not of huge security concern, but I always like to be secure!


Thanks for any tips.

You don't have to worry about this unless you are concatenating in user content. If the user is allowed to type in some string, and you stick that directly into the SQL query, then you DO have a problem. If the user is selecting from a collection (like a drop down list of tables), then you don't need to worry about it. It's only if they are allowed to type in something, and you are sticking that into a query, that you open yourself up to SQL Injection attacks.

So, is that an issue? If so, I'd suggest that you design this in a different fashion. The set of tables that exist in a database can be determined, so it is ALWAYS possible to give the user a list of tables they can choose from.

Assuming your app has knowledge/visibility into the available table names, you could present the user with a dropdown list of tables to choose from. Then you could concatenate what are limited to known table names into the query.

If that isn't an option, perhaps you need to explain more what the end user is doing with your program.

Quote:

---

Originally Posted by OptionBase1

If that isn't an option, perhaps you need to explain more what the end user is doing with your program.

---

Absolutely, especially since you and I both assumed that the table name that was being concatenated in, was coming from the user. If that's not the case, then SQL injection doesn't matter anyways.

Hi,
Thanks for the replies. The detail of this particular piece of the project is building some wrapper functions to interact with a DB. We are still far away from front end user interaction, so building security into this level of abstraction is a consideration to be had. My thought process is, why validate at every single source, when the called function can handle this security? The safe is unlocked inside the house. No problem if all the doors are locked. What if I forget to lock one of them?

That path leads to madness.

SQL Injection only applies if user supplied data can be concatenated into the query. If the table name is not something that the user can type into a textbox, then you don't need to worry about it. A user typing a table name into a textbox...well, that would be mighty weird.

On the subject of why you can't do this in the first place, think about how VB works. If you're calling a method, you can use parameters to pass values into that but you can't use a parameter to specify the method itself. You can't use a parameter to store an identifier. Why should you be able to do it in SQL? Table names and column names in your SQL are identifiers. Parameters are for data values, not for identifiers.

As has already been pointed out, you can get a list of tables from a database and have the user select one from that list and know that it's safe. SQL injection is only an issue for arbitrary user input and if you're allowing arbitrary user input for a table name then any issues that may result are your fault for doing it wrong in the first place.

If you’re worried about SQL Injection Attacks, have a look at this…

https://social.technet.microsoft.com...d-queries.aspx

Quote:

---

Originally Posted by .paul.

If you’re worried about SQL Injection Attacks, have a look at this…

https://social.technet.microsoft.com...d-queries.aspx

---

I don't think that addresses the issue. I think the OP understands the problem of SQL injection and how parameters can protect against it, but that doesn't help when the name of the table is variable.