UPDATE MySQL only works sometimes. Not every time

Question

I'm making a very simple website that's going to be displayed on a TV in a office. The index.php page is a form, and it has three fields. The form method is post and everything there is correct.

Then I have this piece of code on the next page, the one the form points to after submission.

 mysql_select_db("figs", $con);

mysql_query("UPDATE stats SET slots_sold=$_POST[slots_sold], total_figure=$_POST[total_figure], apps_sat=$_POST[apps_sat]");


mysql_close($con);
?>

The problem is that the table is updating SOME of the time and other times it's not, anyone have any ideas why? It's really simple I thought it would just work a charm.

Considering that you are passing values directly from `$_POST` into the query, which is unbelievably dangerous, there are any number of reasons the query could fail. A blank value for any of those would break it, a non-numeric value for any of those would break it. — Michael Berkowski, Jan 22 '13 at 14:10
Consider switching to an API which supports prepared statements like PDO or MySQLi, which will protect you from the SQL injection vulnerability you are currently open to. — Michael Berkowski, Jan 22 '13 at 14:10
before we get to SQL injection, let us first ask why you would use a single row in a database table to store this data in the first place? — paul, Jan 22 '13 at 14:11
Read [this question and its answers](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) thoroughly, and it will help you to solve the issues you are experiencing here. — Michael Berkowski, Jan 22 '13 at 14:25

Shawn Cheever · Answer 1 · 2013-01-22T14:43:58.290

You aren't verifying any of the data passed. Also, I'd recommend using MySQLi for prepared statements to make things a little safer.

// create a new MySQLi object
$mysqli = new mysqli('host', 'user', 'password', 'database');

// create var for each POST array item you need
$slots_sold   = $_POST['slots_sold'];
$total_figure = $_POST['total_figure'];
$apps_sat     = $_POST['apps_sat'];

//check to make sure each field is set
if(isset($slots_sold) && isset($total_figure) && isset($apps_sat))
{    
     // prepare mysqli statement for your data
     if($stmt->prepare("UPDATE stats SET `slots_sold` = ?, `total_figure` = ?, `apps_sat` = ?"))
     {  
         // bind each variable to query, respectively (? is place holder for var)
         // s = string ('sss' means three strings). i = integer if needed
         $stmt->bind_param('sss', $slots_sold, $total_figure, $apps_sat);
         $stmt->execute(); // execute your query
     }
     else
     {
         $stmt->error; // there was an error with the query, show the error
     }
}
else
{
     echo 'You did not fill out all of the fields.';
}

$stmt->close; // close mysqli connection

Hope this will help you a little bit. Depending on the data you are passing, I'd use preg_match to check the data in each. Here are some very simple regular expressions to get you started:

/[a-zA-Z ]+/     (Any letter, lowercase or uppercase including spaces atleast once)
/[a-zA-Z]+/      (Same as above, without spaces atleast once)
/[a-zA-Z0-9]+/   (Any letter, lowercase or uppercase including numbers atleast once)
/[0-9]+/         (Any number atleast once)

preg_match('/[a-zA-Z]+/', $str, $matches); // you can throw this in a for loop to check each var if they all require the same pattern

jcho360 · Accepted Answer · 2013-01-22T15:33:20.360

1

try this:

$sold  = $_POST['slots_sold'];
$total = $_POST['total_figure'];
$app   = $_POST['apps_sat'];

mysql_query("UPDATE stats SET slots_sold='$sold', total_figure='$total', apps_sat='$app'");

also I would recommend to take a look to mysql PDO

edited Jan 22 '13 at 15:33

answered Jan 22 '13 at 14:21

jcho360

3,724
1
15
24

UPDATE MySQL only works sometimes. Not every time

2 Answers2