Solved

php Best way to prevent SQL Injection?

Posted on 2010-08-20
7
877 Views
Last Modified: 2013-12-13
hi,

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

thanks.

0
Comment
Question by:Solutionabc
  • 3
  • 3
7 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33489704
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
0
 

Author Comment

by:Solutionabc
ID: 33489858
is mysql_real_escape_string() better?
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33489926
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Solutionabc
ID: 33489950
Is it necessary to use sprintf() ?
0
 

Author Comment

by:Solutionabc
ID: 33489975
and if I use mysql_real_escape_string ()  do I have to use stripslashes() when displaying information from the db?
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33490118
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.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 33491131
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

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now