SQL Syntax error when deleting row

Question

Ok, so I basically have an HTML form that consists of a hidden input and a submit button. When the button is pressed it will remove a specific row in my MySQL table. The code all actually does the function it should. However, I keep getting a syntax error displaying when I run it. Once I get the error, if I go back the row is gone, which is what I want. I am just not sure how to make it redirect after running like it should, rather than getting the error.

The error:

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 '1' at line 1

Line 1 seems fine to me (hence the confusion).

The PHP code that is running(campaignPostDelete.php):

<?php
$con=mysqli_connect("localhost","username","password","db_name");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$postID = $_POST['postID'];

$delete = mysqli_query($con,"DELETE FROM posts WHERE postID=" . $postID);

if (!mysqli_query($con,$delete))
  {
  die('Error: ' . mysqli_error($con));
  }

header("Location: index.php");
die();

mysqli_close($con);
?>

the HTML form with PHP in case it's needed:

<?php
$con=mysqli_connect("localhost","username","password","db_name");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$campaignID = $_SESSION['campaignID'];


$result = mysqli_query($con,"SELECT posts.postID, posts.postDate, posts.postName, posts.postEntry FROM posts 
   INNER JOIN campaigns ON posts.campaignID= $campaignID
         AND posts.campaignID= campaigns.campaignID ORDER BY postDate desc");

while($row = mysqli_fetch_array($result))
  {
  echo "<div id='campaignPostContainer'>";
  echo "<ul class='campaignPostBox'>";
  echo "<p class='postInfo'>";
  echo "<form name='postDelete' action='campaignPostDelete.php' method='post'>
            <input type='hidden' name='postID' value=" . $row['postID'] . ">
            <input type='submit'>
        </form>";
  echo "Posted on:";
  echo "<li>" . $row['postDate'] . "</li>";
  echo "</p>";
  echo "<p class='postInfo'>";
  echo "Posted by:";
  echo "<li>" . $row['postName'] . "</li>";
  echo "</p>";
  echo "<li class='postEntry'>" . $row['postEntry'] . "</li>";
  echo "</ul>";
  echo "</div>";
  echo "<hr>";
  }


mysqli_close($con);
?>

Dan · Accepted Answer · 2014-02-21 21:30:21Z

2

You are enclosing the ID in single quotes. It is an integer so shouldn't be enclosed in quotes.

$delete = mysqli_query($con,"DELETE FROM posts WHERE postID='$postID'");

should be:

$delete = mysqli_query($con,"DELETE FROM posts WHERE postID=$postID");

However, you are also passing the connection string twice. So instead do this:

$delete = "DELETE FROM posts WHERE postID=$postID";

if (!mysqli_query($con, $delete))
{
  die('Error: ' . mysqli_error($con));
}

But this still leaves you vulnerable to SQL injection. Do at least this to improve this overall:

$delete = sprintf("DELETE FROM posts WHERE postID=%s", mysql_real_escape_string($postID));

if (!mysqli_query($con, $delete))
{
  die('Error: ' . mysqli_error($con));
}

You'll also want to sanitize your other inputs.

edited Feb 21, 2014 at 21:30

answered Feb 21, 2014 at 20:51

Dan

4,7236 gold badges53 silver badges84 bronze badges

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

9 Comments

doriansm Over a year ago

I'm sorry, but where exactly are you talking about?

digitai Over a year ago

Op must change the way you make queries. Please buy a good PHP book, recently published and avoid the use of deprecated garbage code that is a serious threaten to security. Use prepared statements and sanitization for the sake of reinforcing security.

doriansm Over a year ago

OK I changed it to: $delete = mysqli_query($con,"DELETE FROM posts WHERE postID=" . $postID); However, still getting the same error :/ It does still delete the row as it should.

doriansm Over a year ago

@datelligent I appreciate the suggestion. I am still really new to PHP so I have a lot to learn. Do you have any book titles to suggest?

doriansm Over a year ago

@Daи Same thing. It deletes, but I also get the error.

|

Collectives™ on Stack Overflow

SQL Syntax error when deleting row

The error:

The PHP code that is running(campaignPostDelete.php):

the HTML form with PHP in case it's needed:

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The error:

The PHP code that is running(campaignPostDelete.php):

the HTML form with PHP in case it's needed:

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related