SQL error when deleting from MySQL

Question

I am coming across a problem when deleting data from my SQL data. I have tried various versions of my statement but to no avail. Below is the error I am presented with and the statement I am using.

$sql = "DELETE FROM `saved_holidays` WHERE (subscriberID= $user AND title= $check_value)";

//connect to database then execute the SQL statement.
$db->exec($sql);

and the error message is:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '@xml119.com AND
title= Luxurious Jamaican holidays | 40% Discount On Accommodati' at
line 1

I can see that the correct data is being passed but the syntax is wrong. Can anyone help?

Just a guess as I don't know much about PHP. Do you need to enclose $user and $check_value in single quotes? — Chetter Hummin
– Chetter Hummin, Commented Apr 26, 2012 at 13:53

Dmytro Shevchenko · Accepted Answer · 2012-04-26 13:56:31Z

2

$check_value is a string, so you have to enclose it in ' in your query like this:

title = '$check_value'

For security purposes, you should also use mysql_real_escape_string on all string parameters you have. Or even better, use prepared statements: http://php.net/manual/en/pdo.prepared-statements.php

answered Apr 26, 2012 at 13:56

Dmytro Shevchenko

34.9k6 gold badges56 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Los Frijoles · Accepted Answer · 2012-04-26 13:59:51Z

1

You need to put quotations around your variables. It doesn't like spaces.

Depending on the server you are using (MySQL or MSSQL) you have to use backticks, single quotes, or double quotes:

DELETE FROM saved_holidays WHERE (subscriberID="$user" AND title="$check_value")

Also, if you are using PDOs, you should consider using prepared statements:

$statment = $conn->prepare("DELETE FORM saved_holidays WHERE (subscriberID=? AND title=?)"); //$conn has to be your connection ceated by doing new PDO(...connection string...)
$statment->execute(array($user, $check_value));

answered Apr 26, 2012 at 13:59

Los Frijoles

4,8315 gold badges33 silver badges53 bronze badges

3 Comments

Los Frijoles Over a year ago

The PDOs take care of that. If you use the prepared statments, the sql server doesn't process the arguments as a query. As long as the query that you have prepared doesn't contain any variables, there isn't a danger of injection. Now, someone could do XSS or something and it won't prevent that, but at least with the prepared statement you don't have to worry about someone dropping your database when you aren't looking.

Jonathan Leffler Over a year ago

I think you added to your answer between when I copied my comment after your answer and now (quickly enough for the edit not to register as a separate edit). What you've got now, discussing prepared statements, is fine; it is still good to mention the mysql_real_escape_string() function (though prepared statements are indubitably better).

Los Frijoles Over a year ago

The PDOs were in my original answer I believe. Either way, both ways of doing it work but I agree that PDOs are better.

Darren Burgess · Accepted Answer · 2012-04-26 13:58:03Z

0

Amit is correct your statement should look like this;

$sql = "DELETE FROM `saved_holidays` WHERE (subscriberID= '$user' AND title= '$check_value')";

the variable is a string so must be enclosed in single quotes. This should then work for you.

answered Apr 26, 2012 at 13:58

Darren Burgess

4,3426 gold badges29 silver badges43 bronze badges

Collectives™ on Stack Overflow

SQL error when deleting from MySQL

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related