Secure user-input SQL queries

[gamefreak13]

Ok, the internet is a great source of information, but sadly people are misinformed or don't get to the point.

I am creating a user registration script where people can register, then login and access whatever member-only pages I have.

But I'm afraid of some stupid hacker coming after me. So I want to know, once and for all, what is the fool-proof way to clense user-input to prevent SQL injection attacks?

I've heard of the following:

mysql_real_escape_string();
addslashes();
html_special_chars();

I've also read ( [a href=\"http://shiflett.org/archive/184\" target=\"_blank\"]http://shiflett.org/archive/184[/a] ) that hackers can do something relating to the character set and use special character codes to inject commands.

------------------------

So lets say I have this (shortened example):

[code]$username = $_POST['username'];

mysql_query("INSERT INTO members ( `username` ) VALUES ('$username');");[/code]

What do I need to do to [b]$_POST['username'][/b] to make sure hackers can not hack me???

$username = mysql_real_escape_string($_POST['username']);
$username = addslashes($_POST['username']);
$username = html_special_chars($_POST['username']);

[michaellunsford]

Not the expert -- but as I understand them:

mysql_real_escape_string(); = mysql query involving a login or something similar -- this is what you want. As discussed in the manual, It prevents someone from putting a hack value in any field used to directly query mysql. the example on the php.net site was $_POST['password'] = "' OR ''='"; which would result in returning all records.

addslashes(); Might do the same thing -- but the mysql version is supposed to be "better" for protecting mysql queries. This function is referred to in the mysql_real_escape_string.

html_special_chars(); used to convert & to & and the like.

the whole truth -- nothing is ever completely secure. for your purposes, I'd use the mysql thing.

[gamefreak13]

Awesome! Thanks! (More opinons/replies are still welcome though)

Ironically enough, I made my own php mailer script and I had 2 (in the past week, since putting it on my site) e-mails where CC: (and alike) headers were entered into the username field. I IP-banned the submitter, and now limited the amount of characters to that may be entered into the name, email, and subject fields via html. I hope the spambots use the html form and not their own, as I don't know how to limit user input via PHP. I know strlen(); is involved somehow. Eh, I'll get around to it sooner or later.

[b]$username = mysql_real_escape_string($_POST['username']);[/b] it is then! :)

[gamefreak13]

I just tested (without any security things) by registering as:

[b]ff' OR '='[/b]

And when I go to phpMyAdmin to see how it inserted the user, it shows it exactly how I entered it.

Maybe the OR thing didn't work since this is an INSERT command.

I'll try to make a script to SELECT via user-submitted information and see if the OR command works.

More opinions/replies are welcome!

[michaellunsford]

[!--quoteo(post=351522:date=Mar 3 2006, 10:16 PM:name=Gamefreak13)--][div class=\'quotetop\']QUOTE(Gamefreak13 @ Mar 3 2006, 10:16 PM) [snapback]351522[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Ironically enough, I made my own php mailer script and I had 2 (in the past week, since putting it on my site) e-mails where CC: (and alike) headers were entered into the username field. [/quote]


I checked on this ... one way to handle this is to strip all \r and \n from user content like "to" or "from" or anything you're putting in the extra headers section of your mail() script. I'm not completely sure what else can be done -- but it's a start.

[yonta]

[a href=\"http://securephp.damonkohler.com/index.php/Email_Injection\" target=\"_blank\"]Here's[/a] a good article about mail injection with some solutions.

I also use this function to prevent mysql injection (found it in php.net):

function quote_smart($value)
//must be called inside every data function just after opening the connection to the db
{
// Stripslashes
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value)) {
$value = "'".mysql_real_escape_string($value)."'";
}
return $value;
}

Sofia