Deleteing SQL records via HTML form?

[Piranha]

mufar wrote:

Ok, first off, a method like this for "deleting accounts" would be a strictly admin function. Therefore in a secured part of a site.
Would the security risk still be present, even if it required an admin (or trusted personel) to access?
tho the $POST method would have some nice uses and open up a lot of possibilities to the user (if it was somewhat safe).
Is there any way of indirectly putting user submitted data (via $POST) in to a query?

sorry for all the questions, but you guys certianly know what you are talking about, and are quite a help to a beginner like myself

The only system that might work without security is a system that only the programmer himself/herself handles (and very carefully), and that in addition is not accessible from the outside. So yes, the risk will still be present. This is what the articles I linked before is about.

[mufar]

yeah, i like the idea. Im the kind of person that likes to follow "bruce lees law" .
that is, simplicity is the shortest distance between two points.
if anything, following your new idea will tech me a couple of new handy "tools" so ill do that anyways.
I am still completely curious as to what the initial error im getting is. "Query Empty" because as you can see, the query is not empty -.-

[mufar]

Piranha wrote:

The only system that might work without security is a system that only the programmer himself/herself handles (and very carefully), and that in addition is not accessible from the outside. So yes, the risk will still be present. This is what the articles I linked before is about.

kk, ill be checking out them articles.

[Piranha]

It is a new error for me. But the problem is probably that you have 2 queries after each other that may try to remove the same row. If the first removes the row, then the second won't be able to do it, and it is from the second you get the error.

[mufar]

Piranha wrote:

It is a new error for me. But the problem is probably that you have 2 queries after each other that may try to remove the same row. If the first removes the row, then the second won't be able to do it, and it is from the second you get the error.

yeah, i tried, with only one row present, and it still does the same thing, yet still executes the query and deletes the account.

[NogDog]

The error was caused by the 3rd line, below:

mysql_query("DELETE FROM users WHERE username='$_POST[username]'");
mysql_query("DELETE FROM users WHERE email='$_POST[email]'");
if (!mysql_query($sql,$con))

You do two queries by directly entering them as the argument to mysql_query(). Then in the third line you try to do another mysql_query(), but this time specifying the variable $sql as the query string, but you have not defined $sql anywhere in your code before that. What you probably really wanted was:

mysql_query("DELETE FROM users WHERE username='$_POST[username]'") or die(mysql_error());
mysql_query("DELETE FROM users WHERE email='$_POST[email]'") or die(mysql_error());

[mufar]

ahhhh, i c what you are saying. thx.
i gathered that is was trying to run another query.

if yoUr code is used, the following can be removed?:

{
  die('Error: ' . mysql_error());
  }

[mufar]

ok, that code now works as i originally intended. minus the problem that is always gives the "Account Deleted Successfully!" echo even if it doesn't delete anything. (if i enter invalid data)

now to tackle the problem of SQL injection vulnerabilities, and making a way of confirming deletion 😉

just out of curiosity, what could i enter in to the field for account deletion that would demonstrate the use of SQL injection?. So i have a better understanding of what exactly could be done to my database.

This is completly test, and will not be damaging a huge project or anything.

[Piranha]

I must be blind, should have spotted the error right away.

mufar, you could READ THE ARTICLES to understand it.

[mufar]

just got done reading the first Sql Injection article, love how at the end the abuser is refered to as "her. Completely breaking the stereotype (nerdy guy with glasses) is it not?

ps: no im not raggin on ppl with glasses, i wear em myself -.-

[mufar]

Piranha wrote:

mufar, you could READ THE ARTICLES to understand it.

ya, im a very impatient guy, can you tell?
lol just read the first one (in between posts/e-mail notifications)
making a little more sense.

[mufar]

The most dangerous instruction may be DELETE,

lol....:o

[mufar]

ok, i have read the info, and so basically if i was to restrict the following characters somehow:

!@#$%&*()<>:"{}[];',.

or i guess simpler way, restrict all use of special characters in the POST data, (since the username will be text only anyways.)
this should prevent the vulnerability?

the article barely touches on the methods used to validate the data received via a form. So how would i go about verifying the data that comes from the $_POST before then inserting it in to a query? or what would be the best way of achieving this?

I will move on to a method of preventing accidental deletion later, once i tackle the obvious security risk present atm.

thx again, you guys are being pretty helpful.

Think this is the only php site i have found, out of a handful that is as active and has as much user input.

PS:
Just been thinking, couldn't this data be verified at the form? for example, i see often that websites will disable the use of special characters on sign up forms.
tho i know this would not help. if the exploiter decides to just hop on my source code and make their own form. Of course a method in the php would have to compliment it. like i said, the SQL injection article was a huge help, but really only touched on methods to prevent it.

[NogDog]

Never trust client side (browser) validation: a hacker can easily send a HTTP post request and data to your script without ever using your actual form. Client-side validation is only for user convenience; it is still imperative that you "sanitize" all external data before using it.

If you are using a MySQL database, your main tool for prevention of SQL injection is the [man]mysql_real_escape_string/man function. It will take care of escaping any special SQL characters for any values you provide it. In addition, you can verify that all required values have some data, that the length of input strings to not exceed their max size, etc., before ever creating the query.

[mufar]

Well, just got home, decided to play around some more. Using the "mysql_real_escape_string to prevent SQL injection attacks. I also removed the "mysql_close($con) as i found out elsewhere that the PHP will automatically close the connection to the SQL database.
I tested this out, it wont let me use any funky chars that would be used in an SQL injection attack. and since the username will be text only, it seems to work great.
So my question is, have i finally got this spot on? (with a lot of help from you guys) or is there something im overlooking.
It appears to work, and im pretty happy atm. 😃

<?php
$con = mysql_connect("localhost","***","*********");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("hockley_apgame", $con);
mysql_query("DELETE FROM users WHERE username='$_POST[username]'") or die(mysql_error());

//BEGIN SQL INJECTION PREVENTION
	mysql_real_escape_string($_POST[username]);


if (mysql_affected_rows($con) > 0) {
            echo "Account Successfully Removed!\n";
} else {
    echo "ERROR!!!\n";
}
//END SQL INJECTION PREVENTION
?>

[Piranha]

No, there is still a problem. The code is run from the top to the bottom, meaning that first you run your query (with possible sql injection attacks) and after that you validate the variable. And you don't even save the safe variable.

And one other thing: In arrays that are not in strings you should always use ' signs around the names.

$un = mysql_real_escape_string($_POST['username'];
$sql = "DELETE FROM users WHERE username='$id';
$mysql_query($sql) or die(mysql_error());

[edit]And don't be impatient when it comes to programming. You need to take the time to read and understand lots of information to be able to program properly. If you continue to ask the way you have done in this thread (that is without looking at answers and links) people will regard you as a lost case and won't help you out. I don't say this to discurage you, I am saying it to help you get on with the programming.

[mufar]

ok, so if thats the case, how comes it still wont let me use any special characters?
would it still berunning the query? becuase when i say use a =,*&^ or any other funky characters, i get the "ERROR!!!"
i understand what you are saying about the questions, thx for saying so. If you need me to shut up just tell me 😉

I still have a lot to learn, one thing that i think would be a help, is a nice PHP/SQL book, that i can sit and read. I tend to learn better that way, rather than copy and paste from Tutorials online. Tho i still learn a little.

I know this may be taking the topic a little off, but is there any good books out there you would recomend for someone like myself, who wants to get thier foot in the door of PHP/MySQL programing?

Im finding this is an easy language to learn, almost as easy as html, but from what i have been reading, the challenge comes in securing the data.

Im also, clueless as to how to make a user login system, such as would be found on a blog/forum/guestbook ect.

I have many ideas, even a very long term goal of developing my very own browser based game, one day.

Which that being said, could you point me in the best direction to go? Obvoiusly i know going to college for this would be optimal, but this is a spare time project, in between my full time printing career.

your help once again, would be much appreciated.

PS:
Happy Memorial Day

[Piranha]

That you get ERROR means that you have an ERROR. When it works you won't get a message, the query will run but after making sure that there is no sql injection left.

Don't get me wrong. I don't mind questions when people don't understand something. I do mind questions when people haven't tried to solve their problem themselfes or haven't looked at answers already given.

To learn you should not copy and paste from tutorials, you won't learn that way. Instead look at the code, try to understand it and then write your own code. Then you have to understand rather than copy/paste. In addition you make sure that there are no bogus code that could for example give others access to yur database or delete from it.

[mufar]

Piranha wrote:

That you get ERROR means that you have an ERROR. When it works you won't get a message, the query will run but after making sure that there is no sql injection left.

Don't get me wrong. I don't mind questions when people don't understand something. I do mind questions when people haven't tried to solve their problem themselfes or haven't looked at answers already given.

To learn you should not copy and paste from tutorials, you won't learn that way. Instead look at the code, try to understand it and then write your own code. Then you have to understand rather than copy/paste. In addition you make sure that there are no bogus code that could for example give others access to yur database or delete from it.

the "ERROR" is the echo text i set, for when incorrect data is input. (echo)

anyways, any input on the books question?
i think that would prolly be my best route, give me a book, to order, and then tell me to stfu, til i know the basics 😉

I could browse amazon, or even my local bookstore, but someone like yourself im sure would have better input on what i should be looking for.
thx

[Piranha]

I have no idea what books is good. Try to search the board, there are a few threads about this. I would recommend that yo use learn online, no need to buy an expensive book that is most probably outdated anyway. I haven't had a look, but this is a link that I picked up on these forums.

Last I just want to say that you are on your way to learn PHP. Just continue and I'm sure that you will manage 😃