php Best way to prevent SQL Injection?

hi,

I am wondering the best way to prevent SQL injection when inserting users input into MYSQL.

thanks.

SolutionabcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
Hi,

There are many ways but one of the easiest ways is to use stripslashes.

Example

$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
etc
etc


If you are doing a select, you would then do:

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";$result=mysql_query($sql);

Read more here:

http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php
SolutionabcAuthor Commented:
is mysql_real_escape_string() better?
sammySeltzerCommented:
That's one of the sql attack prevention function but, mysql_real_escape_string() for data input protection (sql injection prevention) just as stripsplashes except that mysql_real_escape_string()  works better with your database server encoding method.

However, in the event that magic quotes (magic_quotes_gpc() ) setting is turned on) you use use stripslashes to undo the magic quotes

All in all, if you choose to use mysql_real_escape_string (), as long as you consistently apply mysql_real_escape_string to all user-input variables that you use in a query, you have nothing to worry about as far as sql injection is concerned. It will ensure that the values get inserted into the database exactly as they were entered by the user, without getting mangled in the query by quotes and various other characters.

You can read more about these two, including addslashes()

Hope this helps.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

SolutionabcAuthor Commented:
Is it necessary to use sprintf() ?
SolutionabcAuthor Commented:
and if I use mysql_real_escape_string ()  do I have to use stripslashes() when displaying information from the db?
sammySeltzerCommented:
You can use it, or any of the examples I have already given.

They all represent prevention against one common enemy - sql injection rebuff.

No, you don't you can still use mysql_real_escape_string () .

For me, I use both mysql_real_escape_string () and stripslashes() interchangeably, although I prefer stripslashes(). Just personal preference.
Ray PaseurCommented:
The correct general guidance is "Accept Only Known Good Values" and "Filter Input, Escape Output."

In practice, there are many things that can go wrong besides just SQL injection, and these principles will reduce the risks to near zero if you're careful and consistent about applying them.

First, learn about the PHP filters.  They are not perfect but they are getting better with each new release, and they are almost certainly better than anything we can write.   Examples are on the man pages.
http://us2.php.net/manual/en/function.filter-var.php
http://us2.php.net/manual/en/filter.filters.validate.php

You can extend the filters, too.  best to encapsulate the extensions so you're only calling one kind of filtering routine for each kind of data field.  Here is how I filter data that I think should be an email address (see codesnippet).

Next, consider the kind of information you want in the input string and remove anything that does not make sense.  You can run stripslashes() as many times as you want, but why not just eliminate all the unwanted characters?  If this is an English language name, you need the alphabet, the quote, the period, the hyphen and the comma.  You DO NOT need the < or the ? or { etc.  Set up a REGEX that removes all characters except for those you want to keep, and apply it to all the fields that match that criteria.

Use mysql_real_escape_string() on all fields that go into all queries.  It will not hurt any field.  But use it only once or you may wind up with doubly-escaped data in your data base.  Not a catastrophe, but a nuisance.

Do you expect the field to be a number?  Test it!  Is it unexpectedly negative?  Test it!  Only accept the good values.  What if someone puts in an integer with a comma in it, like 1,275,005?  You don't have to reject this -- the filters are your friend.

Do you expect the field to be a DATETIME? Run it through strtotime() and test to see if the UNIX timestamp passes the reasonableness test.

When you're echoing data from your data base (the kind that came from an external source) use htmlentities() to prevent the accidental placement of malicious code onto your web site.  You may have been smart and safe in every other way, but this last technique is always a good idea.

Threats are always changing, so it's useful to do this search every couple of months.  There are a lot of smart people writing on the topic.
http://lmgtfy.com?q=PHP+Security

HTH, ~Ray
<?php // RAY_email_validation.php
error_reporting(E_ALL);

// A FUNCTION TO TEST FOR A VALID EMAIL ADDRESS, RETURN TRUE OR FALSE

function check_valid_email($email)
{
    // IF PHP 5.2 OR ABOVE, WE CAN USE THE FILTER
    // MAN PAGE: http://us3.php.net/manual/en/intro.filter.php
    if (strnatcmp(phpversion(),'5.2') >= 0)
    {
        if(filter_var($email, FILTER_VALIDATE_EMAIL) === FALSE) return FALSE;
    }
    else
    {
        $regex = '/^[A-Z0-9_-][A-Z0-9._-]*@([A-Z0-9][A-Z0-9-]*\.)+[A-Z\.]{2,6}$/i';
        if (!preg_match($regex, $email)) return FALSE;
    }

    // FILTER or PREG DOES NOT TEST IF THE DOMAIN OF THE EMAIL ADDRESS IS ROUTABLE
    $domain = explode('@', $email);
    if ( checkdnsrr($domain[1],"MX") || checkdnsrr($domain[1],"A") ) return TRUE;

    // EMAIL NOT ROUTABLE
    return FALSE;
}

// DEMONSTRATE THE FUNCTION IN ACTION
$e = '';
if (!empty($_GET["e"]))
{
    $e = $_GET["e"];
    if (check_valid_email($e))
    {
        echo "<br/>VALID: $e \n";
    } else
    {
        echo "<br/>BOGUS: $e \n";
    }
}
// END OF PHP - PUT UP THE FORM
?>
<form method="get">
TEST A STRING FOR A VALID EMAIL ADDRESS:
<input name="e" value="<?php echo $e; ?>" />
<input type="submit" />
</form>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.