Why do I get an SQL parsing error when I use parameters?

Question

I'm working on a database application in Webmatrix. I have a cshtml file which is supposed to update a record in the database. Everything works correctly if I use string concatenation for the SQL command, but I know that's not safe. So, I'm attempting to use parameters instead. But I get an SQL parsing error when I do.

Works:

@{
    string dbName = Request["db"];
    string tble = Request["t"];
    string idName = Request["idn"];
    string id = Request["id"];
    string field = Request["f"];
    string value = Request["v"];

    var db = Database.Open(dbName);
    var result = db.Execute("UPDATE ["+tble+"] SET ["+field+"]='"+value+"' WHERE ["+idName+"]='"+id+"'");
    <text>Result: @result</text>
}

Causes Error:

@{
    string dbName = Request["db"];
    string tble = Request["t"];
    string idName = Request["idn"];
    string id = Request["id"];
    string field = Request["f"];
    string value = Request["v"];

    var db = Database.Open(dbName);
    var result = db.Execute("UPDATE @0 SET @1=@2 WHERE @3=@4",tble,field,value,idName,id);
    @*var result = db.Execute("UPDATE ["+tble+"] SET ["+field+"]='"+value+"' WHERE ["+idName+"]='"+id+"'");*@
    <text>Result: @result</text>
}

Error Message

Please don't do this! A query is where you commit yourself. Your query is the exact programming equivalent of booking a wedding before choosing the bride (or groom, must get into the habit). — bbsimonbb
– bbsimonbb, Commented Feb 23, 2017 at 15:28
Wanting to parameterize table and column names is usually a sign of a broken data model. Data of the same "type", that ought to be stored in one column in one table has instead been dispersed among multiple tables and columns. Oftentimes, you'll then discover that you want to write queries against this data that aren't straightforward because the data is dispersed (having to name many tables and columns in the query and repeat conditions) and that some data has been embedded into these table and column names when it should have been modelled as data. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 24, 2017 at 7:49

mireigi · Accepted Answer · 2017-02-24 07:43:29Z

2

Parameterized SQL commands do not accept table or column names since it would allow for potential SQL injection attacks. It is a security feature.

It is generally a bad design choice to allow table and column names from a form or request to be passed into an SQL string.

A better approach is to use integer values and map those to their corresponding tables or columns.

That way you avoid, or at least make it more difficult for, someone with malicious intent to access sensitive data that was never meant to be exposed through the request or form in the first place.

edited Feb 24, 2017 at 7:43

answered Feb 23, 2017 at 15:14

mireigi

1807 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ronnie Rahman · Accepted Answer · 2019-10-16 20:50:04Z

0

Try this:

var result = db.Execute("UPDATE ["+tble+"] SET ["+field+"]=@1 WHERE ["+idName+"]=@2",value,id);

edited Oct 16, 2019 at 20:50

answered Feb 23, 2017 at 15:20

Ronnie Rahman

3564 silver badges10 bronze badges

Collectives™ on Stack Overflow

Why do I get an SQL parsing error when I use parameters?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related