Placeholder causes MySQL error

[Pedroski55]

If I try this, I get an error:

if($content[$weeknr] == 0) {
		$sql5 = 'UPDATE logon_times_20BECW, allstudents20BE SET logon_times_20BECW.? = allstudents20BE.logon_time WHERE logon_times_20BECW.studentnr = ?';	
		$mystmt5 = $pdo->prepare($sql5);
		$mystmt5->execute([$weeknr, $studentnr]);
		}

The error is:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘Week2’ = allstudents20BE.logon_time WHERE logon_times_20BECW.studentnr = ‘20250’ at line 1

If I do this, everything is fine:

if($content[$weeknr] == 0) {
		$sql5 = 'UPDATE logon_times_20BECW, allstudents20BE SET logon_times_20BECW.' . $weeknr . ' = allstudents20BE.logon_time WHERE logon_times_20BECW.studentnr = ?';
		$mystmt5 = $pdo->prepare($sql5);
		$mystmt5->execute([$studentnr]);
		}

The first placeholder, $weeknr causes the problem. Does anyone know why?

I have other INSERT commands where I have maybe 50 placeholders, no problem.

Maybe it is the . directly before the placeholder?? I tried escaping the . but that didn’t work either.

logon_times_20BECW.?

[phdr]

Prepared query 101 -

1. The main point of a prepared query is to separate the sql query syntax from the data values, so that any sql special characters in a data value cannot break the sql query syntax, which is how sql injection is accomplished.
2. A secondary point is to make executing the same query more efficient when executed more than once in single database session, since the sql query statement is only sent to, parsed, and its execution is planned, only once.

A column name, i.e. what you are trying to supply, is an identifier, not a data value. It is part of the sql query syntax and cannot be supplied via a prepared query place-holder.

If the $weeknr value could contain any sql special characters, you need to validate that it is exactly and only an expected value before putting it into the sql query statement, since nothing you can do to it can protect against sql special characters in it from breaking the sql query syntax. Or you could normalize your data, which has been mentioned in your threads, so that you don’t have a column for each different time period and wouldn’t be trying to dynamically specify a column in a query.

Also, from the error message, with the actual values in it, you are using emulated prepared queries. You need set the PDO emulated prepared query setting to false when you make the database connection so that you are using true prepared queries. Emulated prepared queries are open to sql injection if you haven’t set the character set, that php uses, to match your database table(s) and doesn’t benefit from the 2nd point stated for using prepared queries.

[Pedroski55]

Aha! Thank you, things become a bit clearer

Column names can’t be supplied as variables.

For this particular setting, I supply $weeknr in the login php, e.g. $weeknr = ‘Week1’. $weeknr is not user supplied.

You are right, I was using an old connect-to-database php. The new version has:

$options = [
	 PDO::MYSQL_ATTR_LOCAL_INFILE => true, 	 
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

I need 19 columns of week numbers to cater for each week of term!