SQL Delete Syntax Error, also some PHP and Jquery

Question

I have a database table named 'favoritecats' with the following fields:

id
catName
catId

I am using Jquery to run this function on click event of an element on DOM Ready.

// Delete a Favorite Category from SQL Database
    $('.deleteCatFavs').click(function(){      // On click of .deleteCatFavs
    var actionRequested = "AJAX_delFavCat";    // My Personal PHP Controller Identifier
    var url = "index.php";                     // URL to post to

// Now Im getting the data I want to post into variables.
    var catId = $("input[name=FavCats]:checked").val();
    var rowId = $("input[name=FavCats]:checked").attr("id");

// Now we make the post
    $.post(url, {AJAX_Action: actionRequested, rowId: rowId},
        function(data){
            $("#favCats").fadeIn().html(data);
           });
    });

This all Works Fine,

But below I have the PHP Code to delete the selected rowId from above from the database. Here is where im having the issue, Im sure its a SQL error.

public function AJAX_delFavCat(){

$rowId = isset($_POST['rowId'])?$_POST['rowId']:''; // Get Posted Variable
// Below, I want to delete the posted rowId, from the DB,
$this->database->query("DELETE FROM 'favoritecats' WHERE id='$rowId'");

// My personal Loaders, I need help with the delete query above!!
$data = $this->database->query("SELECT * FROM favoritecats");
$this->load->view('Ajax_addToFavCats.php', $data, $ajax=1);

} // End

The "DELETE FROM 'favoritecats' WHERE id='$rowId'" doesn't work, what am I doing wrong?

[EDIT]
I get the following error through SQL: 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 ''favoritecats' WHERE id='27'' at line 1

Also, How would I write a Jquery function using the $.ajax method instead of the $.post method im using now, does it really make a difference?

As far as I know, $.post is just a shorthand $.ajax function. — Mikk
– Mikk, Commented May 8, 2011 at 13:29
Try to do a var_dump($rowId) of your $rowId to check if you have the correct ID before doing the query. — Tm Cls
– Tm Cls, Commented May 8, 2011 at 13:29
Hi, Yes It picks up the correct string in Jquery, and PHP, I tested both, Im sure its the delete SQL statement, I get the following error. 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 ''favoritecats' WHERE id='27'' at line 1 — Anil
– Anil, Commented May 8, 2011 at 13:35

Community · Accepted Answer · 2017-05-23 10:24:19Z

2

what am I doing wrong?

You've got a SQL-injection security hole.
See: How does the SQL injection from the "Bobby Tables" XKCD comic work?

Change this

$rowId = isset($_POST['rowId'])?$_POST['rowId']:''; // Get Posted Variable
// Below, I want to delete the posted rowId, from the DB,
$this->database->query("DELETE FROM 'favoritecats' WHERE id='$rowId'");

To this

$rowId = isset($_POST['rowId'])?$_POST['rowId']:''; // Get Posted Variable
$rowId = mysql_real_escape_string($rowId);
// Below, I want to delete the posted rowId, from the DB,
$this->database->query("DELETE FROM `favoritecats` WHERE id='$rowId'");

To properly escape your inputs.

Back to your question

$this->database->query("DELETE FROM `favoritecats` WHERE id='$rowId'");

Will fix your error.
Note the use of backticks around tablenames, Normal quotes are not allowed and are in fact a syntax error.

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered May 8, 2011 at 13:33

Johan

77.4k28 gold badges205 silver badges346 bronze badges

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

2 Comments

Johan Over a year ago

@PHP_Guy, I think it's best to ask that in a new question, otherwise everything will get messed up here :-).

Anil Over a year ago

quick update: I had to use $('foo')live("click", function(){}, just figured it out.

a1ex07 · Accepted Answer · 2011-05-08 13:34:35Z

0

Table name should not be in single quotes. Use backticks or leave it as it is .

DELETE FROM 'favoritecats' - wrong
DELETE FROM `favoritecats` - correct
DELETE FROM favoritecats - also correct

answered May 8, 2011 at 13:34

a1ex07

37.4k12 gold badges94 silver badges104 bronze badges

Collectives™ on Stack Overflow

SQL Delete Syntax Error, also some PHP and Jquery

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related