MSSQL“字符串或二进制数据将被截断。”更新时出错

Question

I have a MSSQL table users:

CREATE TABLE users (
  ID int IDENTITY(1,1) NOT NULL,
  firstname nvarchar(20) NOT NULL,
  lastname nvarchar(20) NOT NULL,
  dir bit NOT NULL,
  cc nvarchar(15),
  readyacc bit NOT NULL,
  region nvarchar(50),
  org nvarchar(50),
  suborg nvarchar(50),
  section nvarchar(50),
  title nvarchar(50),
  floor tinyint,
  wkstn nvarchar(50),
  fc nvarchar(15)
);

And I'm trying to update an existing entry with the prepared query:

UPDATE users SET ? = ? WHERE ID=?;

With my parameters as:

Array ( [0] => title [1] => Teleco [2] => 1 )

But it seems as though if the string length is greater than 5 it gives me the error "String or binary data would be truncated.". Eg, Telec works but Teleco does not. When I try the same query in the SQL Management Studio it gives me no errors.

Am I just missing something obvious? Please help

Answer 1

Seeing someone else decided to post an answer, transcribing my comment to an answer.

That's just it, you can't bind tables/columns with UPDATE users SET ? = ? WHERE ID=?;.

Use a safelist if anything.

You can do this though $var="x"; by assigning a pre-defined variable "ahead of time".

Then doing UPDATE users SET '$var' = ? WHERE ID=?;

You see, tables/columns require a hard coded value or a "lookahead" in order to know what it's supposed to use as far as table/column names go, before binding begins. This all happens "after" the query, therefore a "lookahead" can be in the form of a variable.

• FYI: The same applies for MySQL and is not specific to MSSQL.