0

I have a really simple prepared statement as specified below:

$sql = "INSERT INTO transactions(?, ?, ?) VALUES (?, ?, ?)";

but when I go to prepare this:

$stmt = $conn->prepare($sql);

the function returns false (indicating the prepared statement failed), which then causes the Call to a member function bind_param() on bool error later in the code. Why is this syntax failing?

Recessive
  • 1,780
  • 2
  • 14
  • 37
  • first three `?` should be column names – Iłya Bursov Jan 29 '22 at 04:00
  • @IłyaBursov How can I prepare a statement with variable column names then? It's important I can specify column names as the incoming data is not consistent (and I want to use a prepared statement because this is obviously vulnerable to SQLI otherwise). – Recessive Jan 29 '22 at 04:02
  • you cannot, prepare actually builds execution plan for specific columns – Iłya Bursov Jan 29 '22 at 04:03
  • @IłyaBursov Is there another way to run a query with variable column names that is safe from SQLI? – Recessive Jan 29 '22 at 04:04
  • escape column names manually and insert into query – Iłya Bursov Jan 29 '22 at 04:06
  • @Recessive Just hard code a list of possible column names (since you know them ahead of time) and make sure that the user provided values all matching one of those column names. Then you can insert the values into your query. `"INSERT INTO transactions($col1, $col2, $col3) VALUES (?, ?, ?)"` – Cully Jan 29 '22 at 04:06
  • @IłyaBursov You can't "escape column names manually". They will still be vulnerable to SQL injection. – Cully Jan 29 '22 at 04:08
  • @Cully can you elabolate? if you escape all special symbols, surround column names with ` - then you can safely insert everything into query – Iłya Bursov Jan 29 '22 at 04:09
  • @Cully Thanks for the suggestion, that was my plan I was just hoping there was an inbuilt way to do this without having to manually sanitise. Looks like there isn't so I'll just go with this – Recessive Jan 29 '22 at 04:09
  • @IłyaBursov It's not strictly "impossible", but it's almost certain that a naive implementation will mess it up. Even [mysqli_real_escape_string](https://www.php.net/manual/en/mysqli.real-escape-string.php) warns about using it properly. – Cully Jan 29 '22 at 04:15
  • @Cully can you point to exact phrase where escape function warns about it? anyway this function cannot be used to escape column names – Iłya Bursov Jan 29 '22 at 04:18
  • @IłyaBursov "Security: the default character set..." My point is that even with a function in the standard library, there's a warning about using it incorrectly (it won't work if you aren't using the same character set as the server). I wasn't trying to say that it's a good option in this case. – Cully Jan 29 '22 at 04:47

0 Answers0