PHP mysql query syntax errors

Question

I'm fairly new to PHP/MySQL and I seem to be having a newbie issue.

The following code keeps throwing me errors no matter what I change, and I have a feeling it's got to be somewhere in the syntax that I'm messing up with. It all worked at home 'localhost' but now that I'm trying to host it online it seems to be much more temperamental with spaces and whatnot.

It's a simple login system, problem code is as follows:

<?php
session_start();
require 'connect.php';
echo "Test";

//Hash passwords using MD5 hash (32bit string).
$username=($_POST['username']);
$password=MD5($_POST['password']);

//Get required information from admin_logins table
$sql=mysql_query("SELECT * FROM admin_logins WHERE Username='$username' ");
$row=mysql_fetch_array($sql);

//Check that entered username is valid by checking returned UserID
if($row['UserID'] === NULL){
    header("Location: ../adminlogin.php?errCode=UserFail");
}
//Where username is correct, check corresponding password
else if ($row['UserID'] != NULL && $row['Password'] != $password){
    header("Location: ../adminlogin.php?errCode=PassFail");
}
else{
    $_SESSION['isAdmin'] = true;
    header("Location: ../admincontrols.php");
}

mysql_close($con);

?>

The test is just in there, so I know why the page is throwing an error, which is:

`Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in 'THISPAGE' on line 12`

It seems to dislike my SQL query.

Any help is much appreciated.

EDIT:

connect.php page is:

<?php
$con = mysql_connect("localhost","username","password");
if(!$con) {
    die('Could not connect: ' . mysql_error());
}
    mysql_select_db("dbname", $con);
?>

and yes it is mysql_*, LOL, I'll get to fix that too.

Answer 1

You should escape column name username using backtick, try

SELECT * 
FROM admin_logins 
WHERE `Username` = '$username'

You're code is prone to SQL Injection. Use PDO or MYSQLI

Example of using PDO extension:

<?php
$stmt = $dbh->prepare("SELECT * FROM admin_logins  WHERE `Username` = ?");
$stmt->bindParam(1, $username);
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

Answer 2

Sean, you have to use dots around your variable, like this:

$sql = mysql_query("SELECT * FROM admin_logins WHERE Username = '". mysql_real_escape_string($username)."' ");

If you use your code just like this then it's vulnerable for SQL Injection. I would strongly recommend using mysql_real_escape_string as you insert data into your database to prevent SQL injections, as a quick solution or better use PDO or MySQLi.

Besides if you use mysql_* to connect to your database, then I'd recommend reading the PHP manual chapter on the mysql_* functions, where they point out, that this extension is not recommended for writing new code. Instead, they say, you should use either the MySQLi or PDO_MySQL extension.

EDITED:

I also checked your mysql_connect and found a weird regularity which is - if you use " on mysql_connect arguments, then it fails to connect and in my case, when I was testing it for you, it happened just described way, so, please try this instead:

$con = mysql_connect('localhost','username','password');

Try to replace " to ' as it's shown in the PHP Manual examples and it will work, I think!

If it still doesn't work just print $row, with print_r($row); right after $sql=mysql_query() and see what you have on $row array or variable.