How to use PHP Variables in an SQL statement

Question

Before anybody says, I will protect myself against SQL injections, right after I fix this error. I am making an app where news reports are submitted to the database. This page is what removes a report from the database.

What I have tried: Every possible way of adding brackets to the SQL and speech marks. My ICT teacher and I have looked at this for nearly 2 hours and cannot find a fix. I have also searched Google and Stack Overflow but I cannot find an answer.

Ok, so the correct report_id displays when I echo it. When I put the actual id, eg 5, the report is deleted. But when I put $report_id, nothing is deleted.

Please could somebody tell me what correction I have to make to get this to work ?

Here is the code (EDIT: This is the fixed code. I added the hidden field in the form at the bottom, among a few other small changes (like taking out the extra form tag)):

<?php
  require_once('authorize.php');
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>Football Central - Remove a Report</title>
</head>
<body>
  <h2>Football Central - Remove a News Report</h2>

<?php
  require_once('img_details_reports.php');
  require_once('connect_db_reports.php');

   //Assign variables from admin_reports.php using $_GET
   $report_id = $_GET['id'];

    if (isset($_POST['submit'])) {
     if ($_POST['confirm'] == 'Yes') {

      $report_id = $_POST['id'];
      // Delete the image file from the server
      @unlink(IMAGE_UPLOADPATH . $image);

      // Connect to the database
      $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
      or die("Unable to connect to the database."); 

      // Delete the score data from the database
      $query = "DELETE FROM news_reports WHERE report_id = '".$report_id."' LIMIT 1" 
      or die("mysql_query failed - Error: " . mysqli_error());

      mysqli_query($dbc, $query) or die("mysql_query failed - Error: " . mysqli_error());
      mysqli_close($dbc);
     }
    }         

    //Display form to confirm delete
    echo '<p>Are you sure you want to delete the news report?</p>';
    echo '<form method="post" action="removereport.php">';
    echo '<input type="radio" name="confirm" value="Yes" /> Yes ';
    echo '<input type="radio" name="confirm" value="No" checked="checked" /> No <br />';
    echo '<input type="hidden" name="id" value="' . $report_id . '" />';
    echo '<input type="submit" value="Submit" name="submit" />';
    echo '</form>';

    echo '<p><a href="admin_reports.php">&lt;&lt; Back to admin reports page</a></p>';
?>

</body>
</html>

I presume your gets var, submit, confirm and id exists, right....try making an echo of every one of them at the begin of your php script — Hackerman
– Hackerman, Commented May 17, 2013 at 16:48
Whenever I send queries to MYSQL I end them with a semi-colon. Not sure if that's necessary. Give it a go? Also, what is the error code? What do you see if you print $query? — Jimbo
– Jimbo, Commented May 17, 2013 at 16:49

Rikesh · Accepted Answer · 2013-05-17 16:46:16Z

3

Your are mixing two statements. Just try below.

  // Delete the score data from the database
  $query = "DELETE FROM news_reports WHERE report_id = ".$report_id;  
  mysqli_query($dbc, $query) or die("mysql_query failed - Error: " . mysqli_error($dbc));

answered May 17, 2013 at 16:46

Rikesh

26.5k14 gold badges82 silver badges90 bronze badges

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

5 Comments

Matanya Over a year ago

+1. This is the correct answer. the die statement is misplaced

bwoebi Over a year ago

@Matanya That's right, but a string should evaluate to boolean true, so this won't cause the problem...

SAVAFA Over a year ago

Is it correct for sending the database link, see it in here: php.net/manual/en/mysqli.query.php

Matanya Over a year ago

@bwoebi, You are right. lcolli98, can you var_dump($query) and show us the output?

lcolli98 Over a year ago

Hi, everything is fixed after I added the ID into the form ! When I use POST, the ID is not retrieved, only with GET. Do you know why this is ?

SAVAFA · Accepted Answer · 2013-05-17 16:48:32Z

0

You are sending the form with post method and retrieving it with get. That can be the source of the problem.

Also you are not sending the id parameter so, there won't be any value for $_get[id] nor $_post[id]

answered May 17, 2013 at 16:48

SAVAFA

8188 silver badges24 bronze badges

9 Comments

bwoebi Over a year ago

but I don't see any input name="id" there?

George Cummins Over a year ago

The default form method is GET (see this answer), but your point about the lack of an id id valid.

SAVAFA Over a year ago

you are right about the default, but he is setting the method as post already

George Cummins Over a year ago

Ohhh. He has a nested form. There is a form header two lines before without a method=.

lcolli98 Over a year ago

Hi, thanks for the help. I added the input id into the form and it works great !

|

DougM · Accepted Answer · 2013-05-17 16:52:45Z

0

You shouldn't have to wrap the ID in single-quotes, if the ID is a number.

$query = "DELETE FROM news_reports WHERE report_id = '".$report_id."' LIMIT 1"

But that's not the problem. You did not include the ID in your confirmation request, or allow for retrieving the value from a session variable. Add a hidden input box with the id, in your "Display form to confirm delete" section.

(And have a different code branch for confirmation! And a test for an invalid ID! And move this to POST, at the very least!)

answered May 17, 2013 at 16:52

DougM

2,88819 silver badges15 bronze badges

1 Comment

lcolli98 Over a year ago

Hi thanks for that ! When I use POST, the ID is not retrieved, only with GET. Do you know why this is ?

Carson Myers · Accepted Answer · 2013-05-17 16:53:21Z

0

You've got

$query = "..." or die(...);

Why?

Also, you've got two form opening tags -- it's not valid to nest forms.

I'm going to assume that the id variable comes in from some source other than the form because there's no form element that submits it.

Finally, make sure to specify get or post in your form. I'd recommend using post, and then change $_GET["submit"] and $_GET["confirm"] to $_POST["submit"] and $_POST["confirm"].

answered May 17, 2013 at 16:53

Carson Myers

38.7k42 gold badges131 silver badges183 bronze badges

1 Comment

lcolli98 Over a year ago

Hi, the or die was to see if I could get the exact problem :) When I use POST, the ID is not retrieved, only with GET. Do you know why this is ?

Nishu Tayal · Accepted Answer · 2013-05-17 16:54:41Z

0

You need to check following things in your code.

Where is your ID element in the form
You have put POST method in form but retrieving data from $_GET, you should change it to $_POST.

Put mysqli_error after mysqli_query statement.

$query = "DELETE FROM news_reports WHERE report_id = ".$report_id;

mysqli_query($dbc, $query);  or die("mysql_query failed - Error: " . mysqli_error());

Then check the error from mysql if it does not work.

Hope, it will help you in solving your issue.

answered May 17, 2013 at 16:54

Nishu Tayal

20.9k8 gold badges53 silver badges105 bronze badges

1 Comment

lcolli98 Over a year ago

Hi, I added the ID into the form and it works great ! When I use POST, the ID is not retrieved, only with GET. Do you know why this is ?

Collectives™ on Stack Overflow

How to use PHP Variables in an SQL statement

5 Answers 5

5 Comments

9 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

9 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related