Small issue in a select query checking whether form data already exist in the database MySQL/ PHP

Question

I am creating a registration page, and before the data is inserted into my database I need to check to see whether it already exists, but the SQL request I'm generating is missing a ' at the end.

SELECT * from users where firstname = 'Bob' and lastname = 'Smith' and 
username = 'bobsmith' and email = 'bob@test.co.uk' and password = 'testing1

There is no ' showing after the password.

This is my PHP code:

$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$username = $_POST['username'];
$email = $_POST['email'];
$password = $_POST['password'];

$query1 = "SELECT * from users where firstname = '" .
    $firstname .
    "' and lastname = '" . 
    $lastname . 
    "' and username = '" .  
    $username .
    "' and email = '" . 
    $email . 
    "' and password = '" . $password;

echo "<BR>Running query ... <BR>" . $query1;
$result1 = mysqli_query($cxn,$query1);
$numrows1 = mysqli_affected_rows($cxn);

I have tried adding a quote to the end of password like . $password . '"; however this is greying out the rest of the code below so it won't work. How can i fix this?

Update: I know this isn't the most secure way, but it is for a uni assignment and this is the way we are meant to do it.

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jul 12 '17 at 15:57
**Never store plain text passwords!** Please use ***PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html)*** to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). ***It is not necessary to [escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Jul 12 '17 at 15:57
If you parameterize your queries you will not have this problem any more. — Jay Blanchard, Jul 12 '17 at 15:58
You're not adding a quote to the end of the password. It's not going to show up by magic. But I'm with Jay: parameterize, and you'll never have to worry about quoting issues again. — aynber, Jul 12 '17 at 16:00
If an answer solved your problem, consider accepting the answer. Here's how http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work then return here and do the same with the tick/checkmark till it turns green. This informs the community, a solution was found. Otherwise, others may think the question is still open and may want to post (more) answers. You'll earn points and others will be encouraged to help you. *Welcome to Stack!* — Jay Blanchard, Jul 14 '17 at 15:59

score 0 · Answer 1 · answered Jul 12 '17 at 16:33

Here is one way of performing the query using MySQLi prepared statements:

$username = $_POST['username'];
$password = $_POST['password'];

$cxn= mysqli_connect($host, $user, $password, $database);

/* check connection */
if (!$cxn) {
    echo mysqli_connect_error();
    exit();
}

/* prepare the query */    
$stmt = mysqli_prepare($cxn, "SELECT * FROM users WHERE username = ?"); // assuming username is unique
$stmt->bind_param('s', $username);

/* execute prepared statement */
$result = $stmt->execute();
$stmt->store_result(); 

/* get the user info */
$user = $result->fetch_assoc();

If you use PHP's built-in functions to handle password security you can then compare the stored password against $password using password_verify($password, $user['stored_hash']). Make sure you properly setup the database for hashed passwords.

If you're using a PHP version less than 5.5 you can use the password_hash() compatibility pack.

It is not necessary to escape passwords or use any other cleansing mechanism on them before hashing. Doing so changes the password and causes unnecessary additional coding.

Small issue in a select query checking whether form data already exist in the database MySQL/ PHP

1 Answers1