-2

I have problem with update data to database - I'm getting 0000-00-00. To the input field I'm writing data as "2010-08-10". For insert it works correctly, only for update not.

In inserting it works properly also for dates like that, for update this dates also don't work.

Above codes the both queries:

$rec_query = mysql_query ("INSERT INTO `" . $tb_best_ps . "` (id, name, date, author, www, image) VALUES ('','" . mysql_escape_string ($_POST ["name"]) . "','" . $_POST ["date"] . "','" . mysql_escape_string ($_POST ["author"]) . "','" . mysql_escape_string ($_POST ["www"]) . "','" . mysql_escape_string ($_POST ["image"]) . "')");

$query1 = mysql_query ("UPDATE `" . $tb_best_ps . "`
            SET `name` = '" . mysql_escape_string ($_POST ["name"]) . "',
            `date` = " . $_POST ["date"] . ",
            `author` = '" . mysql_escape_string ($_POST ["author"]) . "',
            `www` = '" . mysql_escape_string ($_POST ["www"]) . "',
            `image` = '" . mysql_escape_string ($_POST ["image"]) . "'
            WHERE `id` = '" . $_POST ["edit"] . "'");

Output when I'm using echo on update query: 

UPDATE `swt_best_ps` SET `name` = 'Best Paper Award at ADAPTIVE 2014 for Paper XYZ', `date` = 2010-08-10, `author` = 'David Bowie', `www` = 'http://thinkmind.org/', `image` = 'http://randomweb.com/iaria2014.png' WHERE `id` = '1'
Community
  • 1
  • 1
Beacze
  • 534
  • 3
  • 8
  • 24

1 Answers1

5

You didn't quote your dates, so if you're getting something like 2014-04-08 in your $_POST value, you're actually doing

... date = 2014-04-08 ...

which will be seen as a math operation: two subtractions, and you end up doing

... date = 2002 ...

Since you haven't quoted your dates, nor escaped them, you're both inserting bad data, and vulnerable to SQL injection attacks. ANY external data going into a query string MUST be properly escaped and quoted.

The query building line should be

"`date` = '" . mysql_real_escape_string($_POST['date']) . "'"
          ^---note the added quote                         ^---note the added quote
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Throw a close vote in there. – Kermit Apr 08 '14 at 20:54
  • Thanks a lot, I have thought that in case of date it's acceptable without "". – Beacze Apr 08 '14 at 21:00
  • no. it's impossible for mysql to know `2014-4-4` is a date, and not a double-subtraction. that's why you need the quotes - to make it a string, which mysql will then be able to figure out it's a date. – Marc B Apr 08 '14 at 21:02