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

advertisements

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 @[email protected] WHERE @[email protected]",tble,field,value,idName,id);
    @*var result = db.Execute("UPDATE ["+tble+"] SET ["+field+"]='"+value+"' WHERE ["+idName+"]='"+id+"'");*@
    <text>Result: @result</text>
}

Error Message


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.