Inserting Data into MySQL Table via PHP

Question

I'm trying to add some simple user data into a database via a webpage written in PHP, but the following code (more specifically, line three) breaks the page. Am I using the wrong MySQL function? I'm pretty sure my query is formatted correctly.

mysql_query("CREATE TABLE stats ( userAgent CHAR(20) )");

$userAgent = $_SERVER["HTTP_USER_AGENT"];
mysql_query("INSERT INTO stats VALUES ("$userAgent"));

Answer 1

Should be:

mysql_query("INSERT INTO stats VALUES (".$userAgent.")");

Answer 2

Are you escaping your $userAgent variable?

Data must be "cleaned" before going anywhere near your database.

<?php
// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// Clean
$userAgent = mysql_real_escape_string($_SERVER["HTTP_USER_AGENT"]);
// Query
mysql_query("INSERT INTO stats VALUES ($userAgent)");
?>

Answer 3

The PHP error can be fixed like this (note the dot, it's used to "glue" the strings together):

mysql_query("INSERT INTO stats VALUES (".$userAgent.")");

Also, you should do some SQL Injection protection, the user-agent string is user-defined (there are tools to modify it), so it needs to be sanitized. Further, the user-agent is a string so you need to put it in between single quotes.

mysql_query("INSERT INTO stats VALUES ('" . mysql_real_escape_string($userAgent) . "')");

Another important thing would be error handling - echoing the error description is necessary to find bugs in your SQL syntax.

mysql_query("INSERT INTO stats VALUES ('" . mysql_real_escape_string($userAgent) . "')")
    or die("MySQL Error: " . mysql_error());