Retrieving email addresses with special characters using MySQL query

Question

I am fairly new to PHP and Mysql. The question I am going to ask will be begging for someone to tell me to use prepared statements so first of all let me say I am learning this, but not quite there yet. I have a query that looks to see if an email address is in the database. The email addresses may contain unusual characters like - , / | "" etc etc. I can't seem to retrieve them - here is my code (the repeatemail is coming from a form). Works perfectly with email addresses without this characters.

            $checkemail = $_POST['repeatemail'];
    $checkemail = mysqli_real_escape_string($con, $checkemail);



    //Perform database to see if email exists
        $query  = "SELECT email FROM scorers WHERE email = '{$checkemail}'";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_row($result);
        if ($row[0] == $checkemail){ 
                         echo "found";
                         } else {
                         echo "not found";
                         }

As it stands I have wondered if the escape string is stripping the unusual characters and therefore once its queried it been altered but that doesn't seem to be the case. Also, I have no problem entering addresses like simon.o'malley@nhs.uk but just can't check them with the above code. Looked up many explanations regarding UTF etc but its a bit above my head at this point. Could someone give me a solution to this....how do I alter the code above so it will pick out these funky email addresses? Many thanks

wrap `mysqli_real_escape_string()` around `$checkemail` in your query and see what you get. i.e.; `mysqli_real_escape_string($checkemail)` Are you also escaping when performing an INSERT? — timgavin, Sep 08 '13 at 00:34

score 0 · Answer 1 · answered Sep 08 '13 at 01:20

Got it...this works fine but if any of you have major concerns let me know. Its the magic quotes issue that seemed to be the only problem. All other characters seem fine

    $checkemail = $_POST['repeatemail'];
    $check_email_no_slashes = $checkemail;
    $checkemail = mysqli_real_escape_string($con, $checkemail);
    echo $check_email_no_slashes . "</br>";



    //Perform database to see if email exists
        $query  = "SELECT email FROM scorers WHERE email = '{$checkemail}'";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_row($result);
        if ($row[0] == $check_email_no_slashes){ etc etc etc .......}

Thanks for your input Tim.

OK I spoke too soon....some characters seem to still cause a problem.......any help — GhostRider, Sep 08 '13 at 01:26

kielni · Answer 2 · 2013-09-08T01:27:44.880

0

You really need to use prepared statements. If you don't, you're asking for SQL injection issues (see http://en.wikipedia.org/wiki/SQL_injection). For example, I could send you an email address that would delete all the rows in your table.

Prepared statements aren't hard; here's an example:

$stmt = $mysqli->prepare("SELECT email FROM scorers WHERE email = ?")
// use the string in $checkemail in place of the ?
$stmt->bind_param("s", $checkemail);
// run the query
$stmt->execute();
// put the result into $email
$stmt->bind_result($email);
if ($stmt->fetch()) {
   // found a matching email; do something about it
}
$stmt->close();

You can read more about prepared statements in the PHP docs: http://php.net/manual/en/mysqli.prepare.php

edited Sep 08 '13 at 01:27

answered Sep 08 '13 at 01:20

kielni

4,779
24
21

Brilliant. Thanks Kimberly. I was having some difficulty with this but your clear code above has helped a lot. I do have one question - if you use mysqli_real_escape_strring, is SQL injection still much of a worry? – GhostRider Sep 08 '13 at 09:52
Yes , it's still possible. See http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string – kielni Sep 09 '13 at 04:50

Retrieving email addresses with special characters using MySQL query

2 Answers2