MySQL query won't update database

Question

I have a MySQL query that for some reason won't update the table it's supposed to.

mysql_query("INSERT INTO newsletteroptions (email, exists)
VALUES (\"".$_POST['email']."\", \"".$_POST['exists']."\")");

I've checked over my code and I'm sure that that is the line that is causing errors because the line before it was working -- I went in and edited the information in the database and it still truncated the table (see below)

I don't know why that snippet isn't working, because I copied it over from another document almost exactly the same that I created and tested, and it worked.

I also checked to make sure I spelled the table and field names correctly, and they all are.

This is the full code:

echo "
<form action=\"newsletterinfo.php?status=done\" method=\"post\" name=\"article\" target=\"_self\">
<a title=\"The email address the newsletter is sent from\">Newsletter email address:</a><input type=\"email\" name=\"email\" value=\"".$email."\"><br />
<label for=\"exists\">Activate newsletter?</label> <input type=\"checkbox\" id=\"exists\" name=\"exists\" value=\"true\" ".$checkyes."><br />

<input name=\"\" type=\"submit\" value=\"Update\" />
</form>";
}
else
{

$con = mysql_connect("WITHHELD","WITHHELD","WITHHELD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("newsdb", $con);

    //Add article to database
    mysql_query("TRUNCATE TABLE newsletteroptions");
    mysql_query("INSERT INTO newsletteroptions (email, exists)
    VALUES (\"".$_POST['email']."\", \"".$_POST['exists']."\")");

mysql_close($con);
}

Sigh. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Cfreak, Jun 15 '13 at 01:21
you know its pretty easy to rune your database/life ... i mean your code is ___vulnerable to sql injection___ — NullPoiиteя, Jun 15 '13 at 01:26
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://www.brightmeup.info/article.php?a_id=2). — NullPoiиteя, Jun 15 '13 at 01:27
alway use [http://www.php.net/manual/en/function.mysql-error.php](http://www.php.net/manual/en/function.mysql-error.php) during debugging it will direct you to the answer. — amigura, Jun 15 '13 at 01:27

score 2 · Answer 1 · answered Jun 15 '13 at 01:22

2

You need to read up on sql injection and switch to PDO / mysqli and prepared statements.

The problem you have however, is caused by a reserved word, EXISTS, so the start of your query would have to look like:

INSERT INTO newsletteroptions (email, `exists`) VALUES ...

answered Jun 15 '13 at 01:22

jeroen

91,079
21
114
132

1

Y U NO sql injection warning ? :) – NullPoiиteя Jun 15 '13 at 01:23
Thanks, I'm nowhere near done so I've definitely got fixing the MySQL injection on my list. – Sueño de la Madrugada Jun 15 '13 at 01:25
1

@NullPoiиteя I think I'm going to get a drink too... – jeroen Jun 15 '13 at 01:30

score 1 · Answer 2 · answered Jun 15 '13 at 01:22

The mysql_* functions are deprecated. You should use the new mysqli or pdo functions.

Additionally, you should also escape+sanitize your post variables before concatenating into the SQL statements. Otherwise there is risk of SQL injection.

Finally, try replacing your \" with ' that encloses your string values.

Also, jeroen's answer hits the mark about the exists keyword.

score 1 · Answer 3 · answered Jun 15 '13 at 01:24

1

exists is a reserved word: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

Rename the column or surround the word 'exists' with ` characters

answered Jun 15 '13 at 01:24

Alfie

2,341
2
28
45

MySQL query won't update database

3 Answers3