0

I currently have a database with a table called rooms; which has two attributes: roomID and roomType. I have inserted data into this using MySql which is all fine. I am using PHP and MYSQL in order to show what's currently in the database on the page (which is working just fine) and then a delete.php page where I have a text field for Room ID and Room Type. I wish to delete whatever I prefer from the 'rooms' table however I keep getting the Unknown table 'roomid' in MULTI DELETE error, even though I only have the one table.

Below is my current PHP

<?php
include ('connect.php');

if(isset($_POST['roomID'])){

$roomID = $_POST['roomID'];
$roomType = $_POST['roomType'];

$sql = "DELETE FROM rooms WHERE roomID='"$roomID"' AND roomType='"$roomType"' ";
if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

}

?>

Would appreciate any help

muthambo
  • 1
  • 2
  • Your code is vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection). You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Alex Howansky Apr 17 '17 at 18:35
  • If only you had used prepared statements you wouldn't have had to worry about concatenating the variables properly `WHERE roomID='"$roomID"' AND roomType='"$roomType"'` – Jay Blanchard Apr 17 '17 at 18:37
  • @AlexHowansky yes that is something I need to look into. Thanks for pointing that out – muthambo Apr 17 '17 at 18:37
  • 1
    IMHO this code should result in a parse error. – Paul Spiegel Apr 17 '17 at 18:39

1 Answers1

0

WARNING!

Little Bobby says your script is at risk for SQL Injection Attacks.. Even escaping the string is not safe!

Here's the problem -

If only you had used prepared statements you wouldn't have had to worry about concatenating the variables properly. The following portion of your query line is missing the proper concatenation:

WHERE roomID='"$roomID"' AND roomType='"$roomType"'
Community
  • 1
  • 1
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
  • WHERE roomID='" .$roomID."' AND roomType='".$roomType."' How does this look by adding a . to concatenate the variable? – muthambo Apr 17 '17 at 18:42
  • That is easily tested @muthambo Do it and see. – Jay Blanchard Apr 17 '17 at 18:45
  • I have tested it but still seeing the same issue. I think the problem may reside in the actual query/table itself. The primary key, roomID, is set to auto increment so the ID is given itself, can that be a possible reason as to why it's creating the problem? – muthambo Apr 17 '17 at 18:54
  • No - because each room has a unique ID which you should be able to address. – Jay Blanchard Apr 17 '17 at 18:56