This question already has an answer here:
So lets say in my index.php i have a table which has a delete link which takes you to delete.php?id=x and in delete.php:
$id=$_GET['id'];
$query="delete * from table WHERE id=".$id;
mysqli_query($conn,$query);
Is it safe to do like that? isn't it SQL injection friendly?
</div>
What if you are having a site which deletes the data using your URL. I am unknown, person, but I got to know that this is how you can delete it by just passing id. I can do it randomly, can delete all users or can even crash it. Question is not about GET/POST, question is about what you do to stop doing that from anyone else except you.
Maybe, you can keep validation such as only admin can delete it, maybe for deleting you need to use token,maybe you can encrypt the id and can send it.
Maybe you can do it like this using encoding decoding
<?php
$str = 'YmFzZTY0IGVuY29kZWQgc3RyaW5n';
echo base64_encode($str); //Encode
?>
<?php
$str = 'YmFzZTY0IGVuY29kZWQgc3RyaW5n';
echo base64_decode($str); //Decoding
?>
Edit: The answer was posted from the original question https://stackoverflow.com/revisions/48928503/1 where they didn't bother commenting about the invalid DELETE * syntax and just edited it without even breathing a word after editing their original post.
Yes, you are open to an sql injection.
You could either use a prepared statement which is preferred, or you can simply cast the GET array as an int (edit: given that it will always be an integer). Otherwise, go with a prepared statement; it's your best bet.
$id=(int)$_GET['id'];
However, you have a syntax error here delete *, get rid of the asterisk.
The asterisk is only used for a SELECT statement, not for a DELETE statement.
Using mysqli_error($conn) would have pointed out the syntax error.
Just for argument's sake, table is a MySQL reserved word, just so you know and it should be given special treatment by wrapping it with ticks.
I.e.:
delete from `table` ...
Side note: If the goal is to delete everything with the same id's, then what you have now and minus the asterisk will do just that. But, if the goal is to delete the entire contents of the table, you would need to use TRUNCATE.
Will be better to use something like this:
$id=$_GET['id'];
$mysqli = new mysqli('host', 'db_user', 'db_password', 'db');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s
", mysqli_connect_error());
exit();
}
$stmt = $mysqli->prepare("DELETE FROM table WHERE id=?");
$stmt->bind_param('i', $id);
mysqli_stmt_execute($stmt);
or use PDO:
$pdo= new PDO('mysql:host=db_host;dbname=db_name', $db_user, $db_pass);
$sql = "DELETE FROM yor_table WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
But, sure you need to validate the site_user access to perform this action to your database.
of course it is! first of all use prepare statements to prevent SQL injection, and also its not good idea to delete data via GET request. if you haven't any kind of middleware or authorization, anyone can delete your data by simply sending the request. use POST or DELETE methods instead. also read why shouldn't a GET request change data on the server and how to prevent sql injection
Others have mentioned the SQL injection attack possibility. That's serious and you need to fix it. Make sure your id parameter is an integer before using it. If somebody passes you something else, consider them hostile.
But there's another attack possibility here. Somebody could damage your application database simply by hitting you with a long series of GET operations like this.
https://my.example.com/delete.php?id=1
https://my.example.com/delete.php?id=2
https://my.example.com/delete.php?id=3
...
https://my.example.com/delete.php?id=99999
https://my.example.com/delete.php?id=100000
https://my.example.com/delete.php?id=100001
How do you solve this? There are several ways.
1) make sure your users are logged in to your system, and that the user doing the delete has permission to delete that record. Most login systems feed some sort of session cookie to the user's browser. You should make sure the session cookie is valid before fulfilling any delete request.
2) use a system of nonces. A nonce is "number-once". It's a hard to guess random string or number. Generate URLs in hyperlinks like this, in a page where your legitimate users are invited to click on them.
https://my.example.com/delete.php?id=4635&nonce=slwvlz36t72ixmn
Put the nonces into a table when you generate them. Then, when somebody presents a nonce to one of your pages, check that it's in the table, then delete it from the table. Make the nonces time out: for example, give each one an expiration time ten minutes after you generate it.
If somebody presents a nonce that's not in your table, consider them hostile.
3) there are other ways. You can read about REST service authentication and the like.
(And don't stop asking good questions!)