SELECT Function

[Zanjo]

I've been making a login script (edited from a member here), and ive gotten down to one error, the username and password are always incorrect, because the mysql_num_rows always equals 0 for some reason. I was wondering if you guys could find some error in this. The forms use "Username" for the username bit and "Password" for the password bit. I think the problem is in the $sql but i cant find it.

<?php
session_start();
header("Cache-control: private");
echo "<p><b>Processing Login Information...</b></p>";
$_SESSION['auth']="0";
If ($_POST['Username'] == null)
{
  echo "Please enter your username. ";
  echo '<a href="index.php">Retry</a>';
  exit;
}
If ($_POST['Password'] == null)
{
  echo "Please enter your password. ";
  echo '<a href="index.php">Retry</a>';
  exit;
}

include('database.php');
mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
[B]$sql = "SELECT * FROM login WHERE username = '$_POST[Username]' AND password = password('$_POST[Password]')";[/B]
$result = mysql_query($sql) or die ('Unable to execute query.');
$num = mysql_num_rows($result);
if ($num != '0')
{
    $_SESSION['auth'] = "1";
}

if ($_SESSION['auth'] == "0")
{
  echo "Incorrect Username or Password. ";
  echo '<a href="index.php">Retry</a>';
  exit;
}

else
{
  $ip = getenv('REMOTE_ADDR');
  $logintime = date('j D Y');

  $_SESSION['name'] = $user;
  $_SESSION['auth'] = $valid;
  include('database.php');
  mysql_connect($host, $user, $pass) or die(mysql_error());
  mysql_select_db($db) or die(mysql_error());
  $sqle = "INSERT INTO loginlog (`user`,`logintime`,`IP`)
  VALUES ('$user','$logintime','$ip')";
  mysql_query($sqle);
  echo $_SESSION['name'];
  echo "<br />";
  echo "$ip <br />";
  echo "$date_time <br />";
  echo '<script>
  var targetURL="members.php"
  window.location=targetURL
  </script>';
}

?>

Sorry if its a bit long.

[Rodney_H_]

I would try to concatenate the posted values in your sql statement:

$sql = "SELECT * 
		FROM login 
		WHERE username = '" . $_POST['Username'] . "' 
		AND password = password('" . $_POST['Password'] . "')";

[Roger_Ramjet]

One point hits me immediately, the use of the password column type. This is what the manual has to say on that

" An upgrade to MySQL 4.1 can cause a compatibility issue for applications that use PASSWORD() to generate passwords for their own purposes. Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts. But some applications use PASSWORD() for their own purposes anyway.

If you upgrade to 4.1 and run the server under conditions where it generates long password hashes, an application that uses PASSWORD() for its own passwords breaks. The recommended course of action is to modify the application to use another function, such as SHA1() or MD5(), to produce hashed values."

Meaning that if it works now, upgrading or changing hosts may break your code in the future, so don't risk it.

[Roger_Ramjet]

Now as to your question, did you store the passwords using password()? Sometimes it's the 'obvious' things like that.

I'd start by printing out all the post vars (key and value) to see what you are getting, then run the query in phpmyadmin to test it.

Got to be a reason it's not getting any matches, and it's not syntax or you'd get your error message.

[Zanjo]

Yes, it appears it was the password function. So for MD5 do you just place the variable in the brackets? ( md5($variable1))

[thorpe]

of course. thats how you pass an argument to a sql function.

[Roger_Ramjet]

More or less

$hash = sha1($_POST['Password']);
// or 
$hash = md5($_POST['Password']);

$sql = "SELECT * FROM users WHERE user='" . $_POST['Username'] . "' AND password='" . $hash . "'";

// I always concat like that as it's easier to spot the vars

// many people maintain that you should 'salt' the password in case someone hacks the db, they can't just reverse the hash to get the original password.

$str = 'D5tgabn78' . $_POST['Password'];
$hash = md5($str);

Up to you which hash function you use, and if you salt the password.

Make sure that your password coumn is big enough to hold the hash string.

PS you need a binary comparison of the values if you are comparing them in php.

$p = $row['password'];
if ($hash === $p) {

[Zanjo]

So to hash something you would put md5($password_to_be_hashed).
I dont quite understand the salting bit, I am not sure what that dot after $str = '..' does, and also not sure what the content of $str does. Could you please explain this to me?

[Roger_Ramjet]

'salting' is adding a string to the user submitted password, always the same string, and storing that. When you want to compare the password a user submits, you add the same string, hash it, and compare the 2.
Salting is just an additional layer of security, not strictly neccessary but advisable.

My code takes the string 'D5tgabn78' and concatenates (joins) it to the password input: that is what the dot is, the string concatenation operator in php. Example

$pwd = 's411y'
$salt = 'D5tgabn78'
$salt . $pwd = 'D5tgabn78s411y'

Hash and store the salted password, and then do the same to the user's input when you want to check the login.

If it confuses you then just forget it - but remember the dot.concatenation.operator

[dgrinberg]

I am not sure how secure this script is meant to be, but for completeness sake I think I should mention that your code is a prime candidate for the SQL Injection hack.

You need to make sure that you validate and escape all user input before adding it to your query.

Otherwise, as the link shows, a hacker could find out loads of information about your DB and site.

Appologies if this has been removed to make the code snippet smaller!!! 🙂