Deleting a row sql php

Question

I have a table in which are presented dates from different tables using INNER JOIN.

I want to add a field where you can delete one row. I tried several tutorials but something is wrong with my code. Every time I try to delete a row it says:

Access denied for user(using password: NO)

However, my connection config is ok.

$query="SELECT products.id, products.product_name, products.product_description, 
products.product_price, collections.collection_name, collections.collection_season,
collections.collection_year
FROM products
INNER JOIN products_collections ON products.id = products_collections.product_id
INNER JOIN collections ON collections.id = products_collections.collection_id
ORDER BY products.product_price";

$result=mysqli_query($con,$query);
while ($row=mysqli_fetch_array($result)){
echo "<tr><td>".$row["id"]."</td><td>".$row["product_name"]."</td>
<td>".$row["product_description"].
"</td><td>".$row["collection_name"]."</td><td>".$row["collection_season"]."</td>
<td>".$row["collection_year"]."</td><td>".$row["product_price"].
" USD</td><td><a href='table.php?del=$row[id]'>Delete</a></td></tr>";

}
  if (isset($_GET['del'])){

    $id=$_GET['del'];
    $s="DELETE FROM products WHERE id='$id'";
    $res=mysql_query($s) or die ("Failed ".mysql_error());
    echo "<meta http-equiv='refresh' content='0;url=table.php'>";
}

  ?>

Did you open a mysql connection first? You cannot simply run `mysql_query()` without first running `mysql_connect()`. http://php.net/manual/en/function.mysql-connect.php — Ian, Dec 16 '15 at 20:34
Your first query is using mysqli and your delete is using mysql_* functions. Your also not preparing your statements. Which function are you using? — Liam Sorsby, Dec 16 '15 at 20:35
In 2015, you shouldn't be using the MySQL interface anyway, you should be using MySQLi or PDO with prepared statements/bind variables.... there's never an excuse for learning bad habits — Mark Baker, Dec 16 '15 at 20:36
what rights does your user have? It looks like you are using something like your web user and you have given him *only* read access to the database. give your user more rights. — Max Muster, Dec 16 '15 at 20:45
First, use `mysqli_query` not _mysql_query_ for the _delete_ query. In the same manner as your other selects using mysqli_query , for the delete query, just pass the connection as the first parameter. i.e. `mysqli_query($con, $s);` instead of _`mysql_query($s);`_. You forgot to tell it the connection is all that is wrong. — Ryan Vincent, Dec 16 '15 at 21:41
Were either of the answers helpful? If so you need to click the checkbox next to them or if you keep asking questions without accepting answers it won't let you ask more. — John, Dec 17 '15 at 15:08

John · Answer 1 · 2015-12-16T21:01:16.940

3

1.) You've connected to the SQL database?

2.) Your SQL user authenticated?

3.) You've queried correctly to a unique row and included a LIMIT?

DELETE FROM table WHERE id='987' LIMIT 1;

4.) Your SQL user has permissions to use the DELETE command in SQL?

Whoa, just noticed that you are not escaping data! NEVER TRUST THE CLIENT!!!

MySQL API (Legacy, use MySQLi below)

$id = mysql_real_escape_string($_GET['id']);

MySQLi API

$id = mysqli_real_escape_string($database,$_GET['id']);

You MUST go through all of your SQL queries in your project(s) and ensure you're escaping everything as SQL injection attacks are easily the most pervasive security issue globally. Anyone could obliterate all your data in a single $_POST if they're feeling 14 enough.

edited Dec 16 '15 at 21:01

answered Dec 16 '15 at 20:45

John

10,154
9
79
143

what does the escape do exactly? I don't know what you mean with that – Koen Van Looveren Dec 16 '15 at 21:36
1

I totally agree with you on not trusting the client, or any data for that matter, but escaping in this manner is very error-prone and tedious. Using PDO and perhaps a wrapper-function to avoid repeating code is a MUCH better approach. Preparing statements and binding variables is basically a foolproof way of getting rid of SQL injection problems. – Canis Dec 16 '15 at 21:37
@KoenVanLooveren, useful? - in the _`Related`_ column on this page is a question: [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). It is full of `good stuff` – Ryan Vincent Dec 16 '15 at 22:35
@KoenVanLooveren Yeah, you can have a `$_POST['example'] = 'My data \DELETE ALL FROM SQL` string` (not exactly like that) and because it's not escaped it can damage/destroy your database information. – John Dec 17 '15 at 00:12

score 1 · Answer 2 · answered Dec 16 '15 at 21:34

1

First of: You are mixing mysql_query() and mysqli_query()calls. If you have to use mysql/mysqli stick to mysqli. The "i" is for "improved".

Second: I would really really suggest that you start using the PDO-objects if you can. It is MUCH MUCH safer to use to protect from SQL-injections. Never trust ANY variable you haven't hardcoded to put into your queries WITHOUT preparing the statements and binding the variables first.

See here:

PDO Tutorial for MySQL Developers

Why you should...

PHP.net reference

answered Dec 16 '15 at 21:34

Canis

3,127
1
20
25

I go straight for manual control because I've never trusted frameworks...and when I work with them I immediately find *further* justifications. For the more greenhorn programmers yes...I'm using them when working with others, I like that they handle security for a greenhorn though their code is not intuitive to me personally. – John Dec 16 '15 at 22:02

Deleting a row sql php

2 Answers2