PHP login script SQL error

Question

I'm fairly new to SQL and PHP.

I'm trying to write a simple login script. I have a form in a HTML document that I have proved posts the correct data into the 2 variables required but my script fails when it executes the SQL...

I've also tested the SQL in mysqlWorkbench and I get the result I want ???

Please help.

Here is my script:

<?PHP

$odbc = mysql_connect('localhost', 'root', '') or die ("could not connect to database");
mysql_select_db('examresults', $odbc)  or die("Could not find database");

// username and password sent from form 
$username=$_POST['username']; 
$password=$_POST['password']; 

$sql='SELECT * FROM tuser where username = '.$username.' and password = '.$password.'';

$result = mysql_query($sql, $odbc) or die ("Error in SQL");

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

 //If result matched username and password, table row must only equal 1 row
if($count==1)
{   
    header("location:exammenu.php");
}
else 
{
    echo 'username and password do not match';
}
?>

If you're new I recommend you stop using mysql_connect and (if you are familiar) look up mysqli instead. php.net/manual/en/function.mysql-connect.php — Lucas
– Lucas, Commented Nov 16, 2012 at 11:47
@Matastic You can use MySQL. But you should not use any PHP function whose name starts with mysql_. Read Choosing an API for more information. — Jocelyn
– Jocelyn, Commented Nov 16, 2012 at 11:53

Thomas Glaser · Accepted Answer · 2012-11-16 12:01:04Z

2

Note: mysql_* functions are deprecated, you should not use them anymore. Your code is also vulnerable to SQL Injections.

Using mysql_error instead of just printing out "Error in SQL" would give us (and you) a more detailed sql error message. But most likely it is failing because you forgot to put " " around your strings in the query.

$sql='SELECT * FROM tuser where username = "'.$username.'" and password = "'.$password.'"';

answered Nov 16, 2012 at 12:01

Thomas Glaser

2,2771 gold badge23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

thanks yeh ive been working with integers instead of strings and forgot you need "" around strings thanks alot :)

Kris · Accepted Answer · 2012-11-16 11:56:08Z

If you're really going to need to use mysql, at least sanitize your input. Also note the quotes in the $sql variable. This should work (though not tested):

<?PHP

$odbc = mysql_connect('localhost', 'root', '') or die ("could not connect to database");
mysql_select_db('examresults', $odbc)  or die("Could not find database");

// username and password sent from form 
$username=mysql_real_escape_string($_POST['username'], $odbc); 
$password=mysql_real_escape_string($_POST['password'], $odbc); 

$sql=sprintf('SELECT * FROM tuser where username = "%s" and password = "%s"', $username, $password);

$result = mysql_query($sql, $odbc) or die ("Error in SQL");

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

 //If result matched username and password, table row must only equal 1 row
if($count==1)
{   
    header("location:exammenu.php");
}
else 
{
    echo 'username and password do not match';
}

I suggest using sprintf to format your sql statement to make it easier to spot such errors.

Pankit Kapadia · Accepted Answer · 2012-12-15 09:50:12Z

2

The query should be as below:

 $sql='SELECT * FROM tuser where username = "'.$username.'" and password = "'.$password.'"';

edited Dec 15, 2012 at 9:50

Pankit Kapadia

1,57913 silver badges25 bronze badges

answered Nov 16, 2012 at 11:48

undefined

2,0933 gold badges26 silver badges48 bronze badges

Comments

Soumya Roy · Accepted Answer · 2012-11-16 12:30:29Z

you can try this code. i think it will work correctly.

<?PHP

$odbc = mysql_connect('localhost', 'root', '') or die ("could not connect to database");
mysql_select_db('examresults', $odbc)  or die("Could not find database");

// username and password sent from form 
$username=$_POST['username']; 
$password=$_POST['password']; 

$sql="SELECT * FROM tuser where username = '".$username."' and password = '".$password."'";

$result = mysql_query($sql, $odbc) or die ("Error in SQL");

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

 //If result matched username and password, table row must only equal 1 row
if($count==1)
{   
    header("location:exammenu.php");
}
else 
{
    echo 'username and password do not match';
}
?>

Collectives™ on Stack Overflow

PHP login script SQL error

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related