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
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.
Your query, when the parameters are passed in, it equivalent to this:
UPDATE users SET 'title' = 'Teleco' WHERE ID='1';
Which would not work if you tried running it in the management studio. The error message you're getting is erroneous. As the comment by Fred says, you need to have a safe(white) list of columns that can be updated:
$safe_cols = ['dir', 'cc', 'title'];
if(in_array($col, $safe_cols, true))
{
$stmt = $db->prepare('UPDATE users SET ' . $col . ' = ? WHERE id = ?');
// bind params and execute
}