PHP and MySql trouble

Question

I am having trouble making this seemingly simple MySql query work. Can anyone spot the problem?

<?php
include "config.php";

$offerid = $_POST["offerid"];
$ip = $_SERVER["REMOTE_ADDR"];

mysql_query("INSERT INTO voted (offerid,ip) VALUES (".$offerid.",".$ip.")");
?>

Answer 1

You probably want some single quotes:

 "INSERT INTO voted (offerid,ip) VALUES ('" . $offerid . "','" . $ip . "')"

You should also use intval and mysql_real_escape_string to avoid SQL injection vulnerabilities:

 $sql = "INSERT INTO voted (offerid,ip) VALUES (" .
        intval($offerid). ", '" .
        mysql_real_escape_string($ip) . "')";

Another alternative which may be easier to read is to use sprintf:

 $sql = sprintf("INSERT INTO voted (offerid, ip) VALUES (%d, '%s')",
                $offerid, mysql_real_escape_string($ip));

Answer 2

my guess would be with quotes

mysql_query("INSERT INTO voted (offerid,ip) VALUES (\"".$offerid."\",\"".$ip."\")");

Answer 3

To place a string value into query, you must perform 2 actions on it:

• enclose it in quotes

• and escape special characters.

So, query must be like this:

INSERT INTO voted (text) VALUES ('I\'m a programmer')

Armed with this knowledge, you can easily write a code to make valid query:

$offerid = mysql_real_escape_string($_POST["offerid"]);
$ip = mysql_real_escape_string($_SERVER["REMOTE_ADDR"]);

$sql = "INSERT INTO voted (offerid,ip) VALUES ('$offerid','$ip')"
mysql_query($sql) or trigger_error(mysql_error().$sql);

Note the trigger_error part.
It will provide you with comprehensive information on any error

Answer 4

<?php
include "config.php";

$offerid = $_POST["offerid"];
$ip = $_SERVER["REMOTE_ADDR"];

mysql_query("INSERT INTO voted (offerid,ip) VALUES ('".mysql_real_escape_string  ($offerid)."','".mysql_real_escape_string  ($ip)."')");
?>

This adds the single quote marks around the strings you are inserting - as well as mysql_real_escape_string php function that will escape (add a backslash infront of) any security risk characters.

Answer 5

In addition to using intval(...) and mysql_real_escape_string(...) you could use parameterized statements (or placeholders) using PEAR::DB or PEAR::MDB2:

$dsn = "mysqli://testuser:testpass@localhost/test";
$conn =& DB::connect ($dsn); // using PEAR::DB, though it's been superseded
if (DB::isError ($conn)) {
    die ("Cannot connect: " . $conn->getMessage () . "\n");
}

$result =& $conn->query ("INSERT INTO voted (offerid,ip) VALUES (?,?)", array($_POST["offerid"], $_SERVER["REMOTE_ADDR"]));
if (DB::isError ($result)) {
    die ("INSERT failed: " . $result->getMessage () . "\n");
}

Using placeholders and parameters is pretty common on platforms other than PHP, so it's not a bad idea to understand the basic premise behind them.

If you're interested in using DB modules like these, I'd recommend checking out Writing Scripts with PHP's PEAR DB Module by Paul DuBois. Again, the module it describes is superseded, but I find it's nonetheless interesting and informative.