Dynamically providing column name in VB MySQL programming

Question

So I've got this MySQL query in my code:

Sub MySub(column_name As String)
    Dim sqlQueryString As String = "SELECT * FROM foo WHERE " & column_name & " IS NULL;"

Imagine my SQL table comprises of two columns, column_A and column_B.

I want to pass in "column_A" or "column_B" as a method argument, but this leaves me very open to SQL injection. I looked into passing in the values with .Parameters.Add or .Parameters.AddWithValue, but this results in turning "column_name" into a String value and not a proper column name (because it encapsulates the value in quotations, turning it into a String). How can I work around this?

You're looking to implement the SQL Server function `QUOTENAME` (assuming this is SQL Server). See also http://stackoverflow.com/questions/2547514/correct-escaping-of-delimited-identifers-in-sql-server-without-using-quotename — lc., Sep 16 '14 at 03:13
You could first select the list of actual columns in this table using metadata ciew (assuming MySQL has them) and only substitute in the column if it's valid. — Nick.Mc, Sep 16 '14 at 03:15

score 0 · Answer 1 · edited May 23 '17 at 10:26

You could first select the list of actual columns in this table using INFORMATION_SCHEMA.COLUMNS and only substitute in the column if it's valid.

i.e. this link Get table column names in mysql?

Run this to get the list of columns in your table

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'foo'

Then compare strings in your app where SQL injection won't hurt. If the column really exists then sub it in, otherwise exit the SUB

Dynamically providing column name in VB MySQL programming

1 Answers1