PHP variable in SQL column name

Question

I need to make a SQL update statement in php, and my column name need to be a variable. I get these variables from jQuery ajax post method and i have them in PHP :

$variable = $_POST['variable']; 
$row = $_POST['row']; 
$field = $_POST['field'];

When i echo them out, i get the right values, for example:

echo $variable; 
echo $row;
echo $field;

And i get - martins, 2, firstname. In my database i have column with name - firstname. So, i try to make this statement ( i know that i need to use prepared statement, but that is another question)

$sql = 'UPDATE ajax_example SET '.$field.'= "'.$variable.'" WHERE id = "'.$row.'"';
mysql_query($sql) or die (mysql_error());

If i change '.$field.' with name - firstname, then UPDATE is successful, so $variable and $row is defined correctly , but i need this column name as variable. I have seen about 15 posts, and i have tried each of hose codes but nothing. Is that realy impossible?

Escape your input variables, and please don't use mysql_ functions anymore, use mysqli_ or PDO — JamesHalsall
– JamesHalsall, Commented Feb 16, 2014 at 18:07
1. Don't use mysql_. They're deprecated (and have been for a couple of years). Use mysqli_ or PDO. Second of all, this code is insanely insecure. — h2ooooooo
– h2ooooooo, Commented Feb 16, 2014 at 18:07
Please, please, escape your input variables. Passing them directly into the statement will open doors for a classical attack, called "SQL injection". — fuesika
– fuesika, Commented Feb 16, 2014 at 18:07

JamesHalsall · Accepted Answer · 2014-02-16 18:13:44Z

3

You need to escape your variables, a basic example given your context...

$conn = mysqli_connect(/* config */);
$sql = 'UPDATE ajax_example SET ' . mysqli_real_escape_string($conn, $field).'= "' . mysqli_real_escape_string($conn, $variable) . '" WHERE id = "' . mysqli_real_escape_string($conn, $row) . '"';
mysqli_query($conn, $sql);

answered Feb 16, 2014 at 18:13

JamesHalsall

13.5k4 gold badges43 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3316619 Over a year ago

Thank you! This works! :) But why escape is so necessary? And shoud i use PDO? PDO have some very big pluses?

h2ooooooo Over a year ago

@user3316619 Prepared statements are always better than pasting variables into your query. See Is mysql_real_escape_string sufficient for cleaning user input.

JamesHalsall Over a year ago

@h2ooooooo mysql_real_escape_string isn't sufficient, but mysqli_real_escape_string is fine - because it escapes against the connection that you provide it - whereas the mysql_ variation does not - PDO is far better, but it's a huge deviation from what the OP has

Collectives™ on Stack Overflow

PHP variable in SQL column name

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related