Syntax error near column value Vb

Question

I am passing column name from textbox and i have same column name in table but i am getting syntax error :

 UpdateQuery = "UPDATE Teo SET '" & TextBox2.Text & "' = @DEBIT_HEAD WHERE TEO_NUM = @TEO_NUM"
                    Dim cmd As SqlCommand = New SqlCommand(UpdateQuery, conn)
                    cmd.Parameters.AddWithValue("@DEBIT_HEAD", TextBox3.Text)
                    cmd.Parameters.AddWithValue("@TEO_NUM", TextBox1.Text)

                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()

The problem are the apostrophes `'`. But your code is prone to SQL-Injection. https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — MatSnow, Oct 20 '17 at 05:50
Depends on your needs. It is usually a bad idea to concatenate query-strings with textbox-values. — MatSnow, Oct 20 '17 at 05:54
But i have to match column name with the textbox value for updating that column value — Ishika Sharma, Oct 20 '17 at 05:57
no there is lots of values i have to use so i am using textbox for autocomplete — Ishika Sharma, Oct 20 '17 at 06:12
Then you should validate the column-name before executing the query. Like suggested by @jmcilhinney. — MatSnow, Oct 20 '17 at 06:14
Wanting to parameterize column names is usually a sign that the data model is wrong. What should have been stored as *data* has instead been stored as *metadata*. I.e. that column name *should* instead be a value stored within rows. — Damien_The_Unbeliever, Oct 20 '17 at 06:14

score 4 · Accepted Answer · 2017-10-20T21:09:03.480

4

Remove the single quotes.

UpdateQuery = "UPDATE Teo SET " & TextBox2.Text & " = @DEBIT_HEAD WHERE TEO_NUM = @TEO_NUM"

Or use square brackets instead of the single quotes:

UpdateQuery = "UPDATE Teo SET [" & TextBox2.Text & "] = @DEBIT_HEAD WHERE TEO_NUM = @TEO_NUM"

In order to avoid SQL injection one have to prepare the sql statements and inject the values of the input parameters into them. The preparation process ensures the proper escaping of the user iput. It means that the user input values, whichever they are, will be treated as normal strings. Yes, even if they are some sql specific codes.

Here is a tutorial about prepared statements in vb.net.

Your code is almost safe regarding SQL injection, as I see you prepare your statement first. But, as more users kindly pointed-out, your sql statement is still at risk of SQL injection, given the fact, that you are using a customized column name, which a user can assign by using a text box value. Normally, such a situation should be completely avoided. If not, then you can use a form control suitable to be properly customized only by you, the developer. Like a combobox, or a readonly textbox. If this is not possible, then the user input defining the column name must be properly filtered, sanitized and escaped.

edited Oct 20 '17 at 21:09

answered Oct 20 '17 at 05:47

omg thanks a lot i can't up vote you but +1 for that i was struggling from last many hours thanks again – Ishika Sharma Oct 20 '17 at 05:51
You are welcome. If it worked for you, you can mark the answer as accepted (The check mark under the votes number, at the left of the answer). In general, if an answer suits as a solution for your question and you accept it, then other users benefit of your accepted solution too. Good luck! – Oct 20 '17 at 05:53
ok i will in 6 min. but my code is SQL-Injection what i have to do ? – Ishika Sharma Oct 20 '17 at 05:54
2

You can't use parameters to insert identifiers into SQL code so you have no choice but to use string concatenation, which does leave you vulnerable to SQL injection. If you can, you're better off retrieving the table names from the database yourself and having the user select one from a list. If you can't do that then you should validate the table name before using it to make sure that it contains no SQL keywords. – jmcilhinney Oct 20 '17 at 05:58
2

Backticks ` don't work with MS SQL. You need to use the square brackets `[ ]` instead. – MatSnow Oct 20 '17 at 06:09
Thanks @MatSnow. I come from the mysql world. I didn't want to give a false answer. Is there an alternative for them in sql server? – Oct 20 '17 at 06:14
Aaa, as in vba, i recall. Thank you! Sorry didn't read it at first. – Oct 20 '17 at 06:15
@aendeerei I posted an answer too with the MSSQL specific syntax. But this answer is generally correct so I also upvoted it. – Magisch Oct 20 '17 at 06:16

score 4 · Answer 2 · answered Oct 20 '17 at 06:15

It's regrettable that you have to read column names from a table, it may be worth to try and design your application so you don't have to do that.

Failing that, there is no way for you to parameterize the query as @aendeerei suggested in his answer.

For MSSQL you can use this syntax:

UpdateQuery = "UPDATE Teo SET [" & TextBox2.Text & "] = @DEBIT_HEAD WHERE TEO_NUM = @TEO_NUM"

This is strictly better then omitting the [ and ] delimiters since without those, it will not accept irregular or reserved keyword column names.

Do mind though even with this your code is vulnerable to SQL injection, but if you really have to get the column name through a textbox, there is no avoiding that.

I, too, like your answer! ;-) Especially the first and the last phrase complement my answer. So you have my upvote as well. — , Oct 20 '17 at 06:34

Syntax error near column value Vb

2 Answers2