Is this code safe from any SQL injection attacks?

Question

I write this query to prevent SQL injection. But this code is not working. Can someone tell me where I have gone wrong with this? And I also need to know if this code prevents SQL injection attacks?

// Make sure the email address and username are available:
$q = "SELECT *
        FROM
        (
            SELECT userName, NULL AS email FROM Login
            UNION
            SELECT NULL AS username, email FROM contact
        ) s
        WHERE username = ? OR email = ?";

$stmt = mysqli_prepare($dbc, $q);

mysqli_stmt_bind_param($stmt, 'ss', $username, $email);

// Execute the query:
mysqli_stmt_execute($stmt);

// Get the number of rows returned:
$rows = mysqli_stmt_num_rows($stmt);

if ($rows == 0) { // No problems! Going to next page
}

UPDATE 1 : above code working in this style

            // Make sure the email address and username are available:
            $q = "SELECT *
                    FROM
                    (
                        SELECT userName, NULL AS email FROM Login
                        UNION
                        SELECT NULL AS username, email FROM contact
                    ) s
                    WHERE username = '$username' OR email = '$email'";                  

            $r = mysqli_query ($dbc, $q);

            // Get the number of rows returned:
            $rows = mysqli_num_rows($r);

            if ($rows == 0) { // No problems! Going to next page

}

UPDATE 2 : my username and email come like this

    // Check for a username:
    if (preg_match ('/^[A-Z \'.-]{2,20}$/i', $_POST['username'])) {
        $username = mysqli_real_escape_string ($dbc, $_POST['username']);
    } else {
        $reg_errors['username'] = 'You have not entered your username!';
    }

    // Check for an email address:
    if (!empty( $_POST['email'])) { 
        if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
            $email = mysqli_real_escape_string ($dbc, $_POST['email']);
        } else {
            $reg_errors['email'] = 'You are NOT entered a valid email address!';
        }
    } else {
        $reg_errors['email'] = 'Your email address field can not be empty!';
    }

What exactly isn't working? Do you get an error of the mysql server? Did you check? Did you try to execute the query by hand (for example in phpmyadmin)? — leemes, Feb 02 '13 at 04:01
We need to know what your specific error is to be able to help better. Also assuming this is probably your first time using mysqli, you could be having a problem because you have not imported the mysqli library. — Devon Bernard, Feb 02 '13 at 04:03
Yes I did. I tried it with mysql client (command line). query is working. — TNK, Feb 02 '13 at 04:03
@TharangaNuwan Have a read of http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php — Jess McKenzie, Feb 02 '13 at 04:07
Belongs to [CodeReview](http://codereview.stackexchange.com/) ? — hjpotter92, Feb 02 '13 at 04:13
In this query always return 0 row. But when executing it by hand return over 1 rows. — TNK, Feb 02 '13 at 04:18
Your code doesn't self-evidently check that the prepare operation works. There are reasons it could fail, such as the type of the NULL values in the SELECT isn't known to the DBMS and you might need a cast on it. You've also not shown debugging output such as what is in the variables `$username` and `$email`. Indeed, we have to take it on faith that `$dbc` is a valid connection. — Jonathan Leffler, Feb 02 '13 at 04:20
@JonathanLeffler check my updated code. that code is working — TNK, Feb 02 '13 at 04:27
Your alternative code is categorically not safe from SQL injection unless you've pre-sanitized the variables. I've no idea what else is going wrong...hopefully, someone else will spot the problem. — Jonathan Leffler, Feb 02 '13 at 04:30
Can you tell me how I pre-sanitized that variables. And that method is a alternative method for sql injection attack instead of prepared statement — TNK, Feb 02 '13 at 04:32
There is a function who's name I forget that escapes a string ready for inclusion in an SQL statement as a value. I expect the SQL Injection tutorial covers it (referenced above by Jess McKenzie). Otherwise, you can go to [PHP](http://php.net/) and find it. Or use Google search terms such as 'php sql injection prevention'. [1](http://php.net/manual/en/security.database.sql-injection.php) or [2](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php), where the second is on a Q&A site called Stack Overflow, which you may have heard of. — Jonathan Leffler, Feb 02 '13 at 04:45
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/23822/discussion-between-tharanga-nuwan-and-jonathan-leffler) — TNK, Feb 02 '13 at 04:53

score 1 · Accepted Answer · 2013-02-02T05:24:28.597

1

Can someone tell me where I have gone wrong with this?

I'm going to have a stab in the dark and say that the reason your first code block (prep stmt) is not working is given in the description for mysqli_stmt_num_rows:

Returns the number of rows in the result set. The use of mysqli_stmt_num_rows() depends on whether or not you used mysqli_stmt_store_result() to buffer the entire result set in the statement handle.

...

And I also need to know if this code prevents SQL injection attacks?

Using prepared statements, yes.

edited Feb 02 '13 at 05:24

answered Feb 02 '13 at 05:18

You mean this.. // Execute the query: mysqli_stmt_execute($stmt); //store result mysqli_stmt_store_result($stmt); – TNK Feb 02 '13 at 05:24
I have still a problem. can we chat about this. – TNK Feb 02 '13 at 05:29

Is this code safe from any SQL injection attacks?

1 Answers1