PHP中的SQL查询，类型未知

Question

I would like to store a value which is sometimes a string, sometimes an integer. (In PHP and mySQL)

The variables are set up like this:

$userID = 123; // Unique primary key, always a number
$name = "nameOfTheProperty"; // Always a string
$value = "someText" or 1234; // Depending on the property to save

This works if its a integer property:

$sql = "INSERT INTO saves (`User_ID`, `$name`) VALUES ($userID, $value) ON DUPLICATE KEY UPDATE `$name`=$value";

And this if its a string property:

$sql = "INSERT INTO saves (`User_ID`, `$name`) VALUES ($userID, '$value') ON DUPLICATE KEY UPDATE `$name`='$value'";

How can I get this to work in general? Best in one query.

Answer 1

MySQL won't care if you pass in a 'string' to a numeric field:

INSERT INTO (intfield, charfield) VALUES ('42', '42')

would insert 42 into both fields.

However, what you're doing is basically vulnerable to SQL injection attacks. You should be using a prepared statement and placeholders. Unfortunately, placeholders cannot represent anything other than VALUES, so you'd still need to build part of your query dynamically, e.g.

$safefield = $db->real_escape_string('nameOfField');

$sql = "INSERT INTO yourtable (`$safefield`) VALUES (?)";
$stmt = $db->prepare($sql);
$stmt->execute(array($value_for_field));