1

If I wanted to insert a different letter in group_a_1_pick sql field to be group_b_1_pick using a variable. 

$update = $mysqli->query("UPDATE players SET group_a_1_pick='$pick1' WHERE food='$player'");

$groupletter = b

$update = $mysqli->query("UPDATE players SET group_$groupletter_1_pick='$pick1' WHERE food='$player'");

Is this possible?

Outdated Computer Tech
  • 1,996
  • 4
  • 25
  • 39
  • I'm not sure what you are trying to do but anyway... don't concatenate data to queries, use prepared statements instead. – Spoody Apr 25 '18 at 19:08
  • yes it is possible. If you want to check, you can assign whole query string to variable and check if thats what you want and then you can pass it in the query. – Aman jaura Apr 25 '18 at 19:10
  • Something like below $sql = "UPDATE players SET group_{$groupletter}_1_pick='$pick1' WHERE food='$player'" – Aman jaura Apr 25 '18 at 19:18
  • Sure it can be done, but are you sure you want to do this and in that way? you're leaving yourself open to an sql injection here. Plus, the column needs to exists. If someone were to enter a value for it that doesn't match the column set, you're going to get errors. – Funk Forty Niner Apr 25 '18 at 19:19
  • @funkfortyniner so what would you suggestion I do? – Outdated Computer Tech Apr 25 '18 at 19:21
  • David's answer for me is by far the better method. But, again... you're going to need to make sure the column exists. – Funk Forty Niner Apr 25 '18 at 19:22

3 Answers3

2

For data values you should certainly use prepared statements and query parameters. For example, your $pick1 and $player values fall into this category. To learn about prepared statements, this is a great place to start.

However, that's not really what you're asking. You're asking about using variables for schema objects (column names, table names, etc.) instead of data values. Those can not be handled by query parameters.

The good news is that you can use a variable to do exactly what you're trying to do. For example:

$query = "UPDATE Table1 SET Col_{$someVar}_X = ?";

This is just interpolating a value into a string like any other string in PHP. But then, given concerns about SQL injection, the real question becomes:

Where does $someVar come from?

It should never come from user input. Fortunately, while data values can be anything under the sun, schema objects are finite and known ahead of time. So you can take a simple whitelist approach. When the logic is being processed, you can have a list of all possible valid values for $someVar. (Either hard-coded or generated from the database schema.)

Then you can compare any input value with the values in that list. If there's no match, return an error. If there is a match, use the matched value from the list. That way you're only ever using known safe values, which is no different than using a literal string.

After all, while this may set off some SQL injection alarms at first glance:

$query = "UPDATE Table1 SET Col_{$someVar}_X = ?";

The context of those alarms is a bit different if you know where the value is coming from:

$someVar = "123";
$query = "UPDATE Table1 SET Col_{$someVar}_X = ?";

SQL injection is not about always using prepared statements as a magic wand. It's about always controlling the SQL code your application executes, wherever that code comes from. As long as you control the code and the user doesn't, you're okay.

David
  • 208,112
  • 36
  • 198
  • 279
  • 1
    don't forget the `WHERE` clause ;-) unless they want to update their entire database. – Funk Forty Niner Apr 25 '18 at 19:23
  • @FunkFortyNiner: Of course :) Admittedly I was focused less on the exact code the OP was writing (mostly in an attempt to *not* create something one could copy/paste, particularly since SQL injection is a concern here) and more on the structure of the solution. – David Apr 25 '18 at 19:24
  • No problemo ;-) just a bit of a side note on my part :-) – Funk Forty Niner Apr 25 '18 at 19:26
1

It isn't working because $groupletter_1_pick is a valid variable name. With variables in a double quoted string, PHP will identify the longest possible valid variable name after the $. It won't stop at $groupletter just because that variable exists.

You can bracket the variable to specify it in the string.

... SET group_{$groupletter}_1_pick ...

I would really recommend rethinking your table design, though. Column names like that indicate data that should probably be normalized into a related table.

Don't Panic
  • 41,125
  • 10
  • 61
  • 80
1

This is what you need. Just wrap your variable by curly braces i.e {$variable}. See about complex curly syntax http://www.php.net/manual/en/language.types.string.php#language.types.string.parsing.complex

$groupletter = 'b';
$pick1 = 'some_value_goes_here';
$player = 'some_other_value_goes_here';
$string = "UPDATE players SET group_{$groupletter}_1_pick='$pick1' WHERE food='$player'";
$mysqli->query($string);
A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103