mysql_real_escape_string

[steviemac]

Hi

Can anyone tell me if the use of mysql_real_escape_string used the following way:

 

$find = mysql_real_escape_string($find);
$data = mysql_query("SELECT * FROM table WHERE upper(email) ='$find' LIMIT 1");

 

is better, worse or the same as this:

 

$data = mysql_query("SELECT * FROM table WHERE upper(email) = "'" . mysql_real_escape_string($find) . "'" LIMIT 1");

 

Thanks in advance for the input.

[Psycho]

Aside from the fact that the 2nd method you've shown won't work due to syntax errors, I assume you meant the use of mysql_real_escape_string() concatenated within the query string itself. No, there is nothing 'wrong' with it. I personally hate the second approach as it makes it difficult to read the query in an easy manner.

 

But, then again I think both of those approaches are poor. I always advise against ever creating the query inside the mysql_query() function. I always create my queries as string variables then use those in the mysql_query() function. Then if there are any problems I can echo the query to the page for debugging purposes.

 

By the way, this would be the correct syntax

$data = mysql_query("SELECT * FROM table WHERE upper(email) = '" . mysql_real_escape_string($find) . "' LIMIT 1");

 

And, there is another option as well - sprintf(). I don't usually use this myself, but it does make the use of mysql_real_escape_string() to create your query - without the need to create temp variables - very easy.

$query = sprintf("SELECT * FROM table WHERE upper(email) = '%s' LIMIT 1",
                  mysql_real_escape_string($find));
$data = mysql_query($query);

 

[steviemac]

OK thanks for your help.  I am learning this mainly from reading the internet and trial and error.

[requinix]

OK thanks for your help.  I am learning this mainly from reading the internet and trial and error.

With PHP there are many, many ways of doing something. Narrow it down to the methods that actually work and are safe (feel free to ask for help on that part) then pick the one you like best.

[fenway]

All of these are error-prone -- write a simple DB wrapper that takes a hash and you're done.

[steviemac]

OK if I'm doing something like this:

<?
$email = $_POST['email'];
$response = $_POST['response'];
$id_number = $_POST['id_number'];
if(isset($submit)){
[color=red]$email=mysql_real_escape_string($email);
$response=mysql_real_escape_string($response);
$id_number=mysql_real_escape_string($Id_number);[/color]

$status = "OK";
$msg="";

if ( strlen($response) < 1  ){
$msg=$msg."You did not enter any data<br />";
$status= "NOTOK";}					
if ( $query="SELECT response,userName,email FROM myuser WHERE [color=red]response = '" . mysql_real_escape_string($response) . [/color]"'");

 

is the mysql_real_escape_string needed twice once stated above and then in the query or do I just need it one time.

 

Thanks

[Psycho]

You only need to do it one. If you do it multiple times you will introduce extraneous characters into the stored results. In your sampel code above there are some inefficiencies. No need to set the POST value to a variable. Then set the variable to itself with the mysql_real_escape_string(). Plus, you should almost always trim() user submitted data - else a 'space' would be interpreted as a valid value. Also, try to avoid 'flag' variables using text such as yes/no or OK/NOTOK. Use the Boolean true/false values.

 

if(isset($submit))
{
    $email = mysql_real_escape_string(trim($_POST['email']));
    $response = mysql_real_escape_string(trim($_POST['response']));
    $id_number = mysql_real_escape_string(trim($_POST['id_number']));
    $valid = true;
    $msg = '';

    if(empty($response))
    {
        $msg .= "You did not enter any data<br />";
        $valid = false;
    }

    if($valid)
    {
        $query = "SELECT response, userName, email
                  FROM myuser
                  WHERE response = '{$response }'";
        $result = mysql_query($query);

[steviemac]

Thank you very much for you time and input.