Unknown column 'abcdefg' in 'where clause'23

Question

Ok so I am having some issues with a query. I am working to learn MySQLi as well so there may be some errors. I have a table named Authentication and in it, it has these columns

||id
||UserName
||Password

When running the query I am getting my username as the column name so it gives the unknown column error. I can not seem to see what is wrong with my code. Any help is appreciated.

 <?php
// Report all errors
error_reporting(E_ALL);
session_start(); // Start PHP
// Get info sent to server from login form.
$my_username = $_POST['username'];
$my_password = $_POST['password'];
// MD5 Encrypt the password.
$my_password_md5 = md5($my_password);
// Connect to DB
$db = new MySQLi('localhost', 'user', 'password!', 'database');
if ($db->connect_error) {
    $error = $db->connect_error;
}

//SQL query
$sql = <<<SQL
    SELECT UserName
    FROM `Authentication`
    WHERE `username` = $my_username HAVING `username` = $my_password_md5
SQL;

$result = $db->query($sql) or die($db->error.__LINE__);

if($result = $db->query($sql))
$rows=mysqli_fetch_assoc($result);
// Count how many rows match that information.
$count=mysqli_num_rows($result);
// Check if there are any matches.
if($count==1)
{// If so, register $my_username, $my_password and redirect to the index page.
ini_set("session.gc_maxlifetime", "18000"); 
session_cache_expire(18000);
$cache_expire = session_cache_expire();
$_SESSION['username'] = $my_username;
$_SESSION['id'] = $rows['id'];
header("location:http://somesitegoeshere.com");
}

// If not, redirect back to the index page and provide an error.
else {
header("location:http://somesitgoeshere.com?err=1");
}
?>

string value needs to be wrapped in single quote. – Abhik Chakraborty Feb 18 '15 at 16:49 — Abhik Chakraborty, Feb 18 '15 at 16:49

castis · Accepted Answer · 2015-02-18T17:06:37.633

2

$sql = <<<SQL
    SELECT UserName
    FROM `Authentication`
    WHERE `username` = $my_username HAVING `username` = $my_password_md5
SQL;

You forgot to quote $my_username. so your query looks like WHERE 'username' = abcdefg HAVING...

Mysql thinks you're trying to compare to a column, put your username in quotes. Also put your password in quotes so it doesnt think your password is a column.

$sql = <<<SQL
    SELECT UserName
    FROM `Authentication`
    WHERE `username` = "$my_username" HAVING `username` = "$my_password_md5"
SQL;

edited Feb 18 '15 at 17:06

answered Feb 18 '15 at 16:51

castis

8,154
4
41
63

what are the \ for? Seems good that one goes outside of the quote and one goes in it? – Tyler Radlick Feb 18 '15 at 17:03
the \ is an [escape character](http://php.net/manual/en/regexp.reference.escape.php). However, for this scenario I put them in by mistake and they are not needed. – castis Feb 18 '15 at 17:06
1

Ok gotcha. Thank you so much this code worked like a charm! – Tyler Radlick Feb 18 '15 at 17:15

Unknown column 'abcdefg' in 'where clause'23

1 Answers1