loggin help please!!! You have an error in your SQL syntax;

MasonPrice · January 8, 2009

???

hey whats up guys? im noobie to php & mysql i have been recently trying to create a loggin/register page but i keep getting this annoying error when i try to register:----> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'demo'' at line 1

i have been going tru and tru the code trying to figure the problem out my self but i still dont see it can someone please help me?

here is the code:

login.php

<title>Untitled Document</title>
</head>
<body>
<?php

session_start();
include "conn.inc.php";

if (isset($_POST['submit'])) {
  $query = "SELECT username, password FROM user_info".
           "WHERE username = '" . $_POST['username'] . "' ".
	   "AND password = (PASSWORD('". $_POST['password'] . "'))";
  $result = mysql_query($query)		
     or die(mysql_error());

if (mysql_num_rows($result) == 1) {
  $_SESSION['user_logged'] = $_POST['username'];
  $_SESSION['user_password'] = $_POST['password'];
  header ("Refresh: 5; URL" .  $_POST['redirect'] . "");
  echo "You are being redirected to the original page requested!<br>";
  echo "(If your browser doesn't support this , " .
       "<a href=\"" . $_POST['redirect']. "\">click here</a>)";
} else {
   
?>
</body>
</html>


<html>
<head>
<title>Hello Page</title>
</head>
<body>
<p>
   Invalid Username and/or Password<br>
   Not registered?
   <a href="register.php">Click Here</a> to registerd.<br>
   <form action="user_login.php" method="post">
     <input type="hidden" name="redirect"
       value="<?php echo $_POST['redirect']; ?>">
    Username: <input type="text" name="username" ><br>
    Password: <input type="password" name="password"><br><br>
    <input type="submit" name="submit" value="Login">
  </form>
</p>
</body>
</html>

<?php 
  }
} else {
  if (isset($_GET['redirect'])) {
    $redirect = $_GET['redirect'];
  } else {
    $redirect = "index.php";
  }
?>

<html>
<head>
<title>Hello Page</title>
</head>
<body>
<p>
  Login below by supplying your username/password...<br>
  Or <a href="register.php">Click here</a> to register.<br><br>
  <form action="user_login.php" method="post">
    <input type="hidden" name="redirect"
     value="<?php echo $redirect; ?>">
   Username: <input type="text" name="username"><br>
   Password: <input type="password" name="password"><br><br>
   <input type="submit" name="submit" value="Login">
   </form>
</p>
</body>
</html>
<?php
}
?> 
conn.inc.php
<title>conn.inc.php</title>
</head>
<body>
<?php

$connection = mysql_connect("host", "name", "pass")
  or die(mysql_error());
$db = mysql_select_db("databasename")
  or die(mysql_error());

?>
</body>
</html>
register.php
<?php 
session_start();
ob_start();
include "conn.inc.php";
?>

<head>
<title>Hello Page</title>
</head>
<body>
<?php
if (isset($_POST['submit']) && $_POST['submit'] == "databasename") {
  if ($_POST['username'] != "" &&
      $_POST['password'] != "" &&
      $_POST['first_name'] != "" &&
      $_POST['last_name'] != "" &&
      $_POST['email'] != "" ) {


   
   $query = "SELECT username FROM user_info" . 
            "WHERE username = '". $_POST['username'] . "';";
   $result = mysql_query($query)
          or die(mysql_error());
	  
if (mysql_num_rows($result) != 0) {
?>
<p>
   <font color="#FF0000">The Username,
   <?php echo $_POST['username']; ?>, is already in use, please choose 
   another!</br></font>
   <form action="register.php" method="post">
   Username: <input type="text" name="username"><br>
   Password: <input type="password" name="password"
              value="<?php echo $_POST['password']; ?>"><br>
   Email: <input type="text" name="email"
          value="<?php echo $_POST['email']; ?>"><br>  
   First Name: <input type="text" name="first_name"
          value="<?php echo $_POST['first_name'];?>"><br>
   Last Name:<input type="text" name="last_name"
          value="<?php echo $_POST['last_name'];?>"><br>
    City:<input type="text" name="city"
          value="<?php echo $_POST['city'];?>"><br>
    State:<input type="text" name="state"
          value="<?php echo $_POST['state'];?>"><br>
    Hobbies/Interests:(Choose at least one)<br>
    <select name="hobbies[]" size="10" multiple>
     <option value="Golfing"<?php
  if (in_array("Golfing", $_POST['hobbies'])){
   echo "selected";   
     } ?>>Golfing</option>
     <option value="Hunting"<?php
     if (in_array("Hunting", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Hunting</option>
         <option value="Reading"<?php
     if (in_array("Reading", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Reading</option>
         <option value="Dancing"<?php
     if (in_array("Dancing", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Dancing</option>
         <option value="Internet"<?php
     if (in_array("Internet", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Internet</option>
         <option value="Flying"<?php
     if (in_array("Flying", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Flying</option>
         <option value="Traveling"<?php
     if (in_array("Traveling", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Traveling</option>
         <option value="Excersising"<?php
     if (in_array("Exercising", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Exercising</option>
         <option value="Computers"<?php
     if (in_array("Computers", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Computers</option>
         <option value="Other Than Listed"<?php
     if (in_array("OtherThan Listed", $_POST['hobbies'])){
   echo "selected";   
         } ?>>Other Than Listed</option>
   </select><br><br>
   <input type="submit" name="submit" value="Register">  
   <input type="reset" value="Clear">
  </form>
</p>
<?php
} else {
$query = "INSERT INTO user_info (username, password, email, ".
          "first_name, last_name, city, state, hobbies) ". 
          "VALUES ('". $_POST['username'] ."',".
          "(PASSWORD('" . $_POST['password'] ."')), '".
          $_POST['email'] . "','" . $_POST['first_name'] . 
          "', '" .$_POST['last_name'] . "', '" . $_POST['city'].
          "', '". $_POST['state'] . "', '" .
          implode(", ", $_POST['hobbies']) . "');";
$result = mysql_query($query)
  or die (mysql_error());
$_SESSION['user_logged'] = $_POST['username'];
$_SESSION['user_password'] = $_POST['password'];
?>
<p>
  Thank you, <?php echo $_POST['first_name'] . " " .
  $_POST['last_name']; ?>for registering!<br>
<?php
       header ("Refresh: 5; URL=index.php");
   echo"your registration is complete! " .
       "you are being sent to the page you requested!<br>";
       echo"(If your Browser doesn't support this, " .
       "<a href=\"index.php\">click here</a>";
   die();
 }
   } else {
?>

<p>
<font color="#FF0000"><b>The Username, Password, Email, First Name,
and Last Name fields are required!</b></font>
<form action="register.php" method="post">
Username:<input type="text" name="username"
             value="<?php echo $_POST['username']; ?>"><br>
Password:<input type="password" name="password"
             value="<?php echo $_POST['password']; ?>"><br>
  Email:<input type="text" name="Email"
             value="<?php echo $_POST['email']; ?>"><br>

First Name:<input type="text" name="first_nAme"
             value="<?php echo $_POST['first_name']; ?>"><br>

Last Name:<input type="text" name="last_name"
             value="<?php echo $_POST['Last_name']; ?>"><br>

City:<input type="text" name="city"
             value="<?php echo $_POST['city']; ?>"><br>

State:<input type="text" name="state"
             value="<?php echo $_POST['state']; ?>"><br>

Hobbies/Interests: (choose at least one) <br>
<select name="hobbies[]" size="10" multiple>
    <option value="Golfing"<?php
  if (in_array("Golfing", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Golfing</option>
    <option value="Hunting"<?php
  if (in_array("Hunting", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Hunting</option>
    <option value="Reading"<?php
  if (in_array("Reading", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Reading</option>    
     <option value="Dancing"<?php
  if (in_array("Dancing", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Dancing</option>
     <option value="Internet"<?php
  if (in_array("Internet", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Internet</option>     
     <option value="Flying"<?php
  if (in_array("Flying", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Flying</option>  
     <option value="Traveling"<?php
  if (in_array("Traveling", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Traveling</option>  
     <option value="Exercise"<?php
  if (in_array("Exercise", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Exercise</option>  
     <option value="Computers"<?php
  if (in_array("Computers", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Computers</option> 
     <option value="Other Than Listed"<?php
 if (in_array("Other Than Listed", $_POST['hobbies']))  {
      echo "selected";
	} ?>>Other Than Listed</option> 
   </select><br><br> 
   <input type="submit" name="submit" value="Register">  
   <input type="reset" value="Clear">
</form> 
</p>
<?php
}
} else {
?>
<p>
Welcome to the registration page!<br> 
The Username, Password, Email, First Name, and Last Name fields
are required!
<form action="register.php" method="post">
  Username:<input type="text" name="username"><br>
  Password:<input type="password" name="password"><br>
  Email:<input type="text" name="email"><br>
  First Name:<input type="text" name="first_name"><br> 
  Last Name:<input type="text" name="last_name"><br>
  City:<input type="text" name="city"><br>
  State:<input type="text" name="state"><br>
  
Hoddies/Interest: (Choose at least one)<br>
<select name="hobbies[]" size="10" multiple>
   <option value="Golfing">Golfing</option>
   <option value="Hunting">Hunting</option>
   <option value="Reading">Reading</option>
   <option value="Dancing">Dancing</option>
   <option value="Internet">Internet</option>
   <option value="Flying">Flying</option>
   <option value="Traveling">Traveling</option>
   <option value="Excercising">Excercising</option>
   <option value="Computers">Computers</option>
   <option value="Other Than Listed">Other Than Listed</option>
</select><br><br>
<input type="submit" name="submit" value="Register">  
<input type="reset" value="Clear">
  </form>
</p>
<?php
}
?>
</body>

My Database is called "registration"

CREATE TABLE user_info (
email varchar (50) NOT NULL,
username varchar (50) NOT NULL,
password varchar (255) NOT NULL,
first_name varchar (50) NOT NULL,
last_name varchar(50) NOT NULL,
city varchar(50) NULL
state varchar(50) NULL
hobbies varchar(255) NULL
id int(10) NOT NULL defaut '0'
);

(edited to add

tags)

btherl · January 8, 2009

What are you trying to do when you get the error? Register? Login?

To debug the problem, try printing out each mysql query before executing it. It's likely you'll be able to see the problem.

MasonPrice · January 9, 2009

both loggin and registerwhen i try to loggin i get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Mason' AND password = (PASSWORD('test'))' at line 1

when i register i get the above error

its driving my crazy the funny thing is i copy this from a book

i went line for line to make sure i did it exactly how its in the book and still i cant find the error but i will try it out like you say i will get back to ya thanks for the reply/advice.....b rigth bac

MasonPrice · January 9, 2009

both loggin and register when i try to loggin i get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Mason' AND password = (PASSWORD('test'))' at line 1

when i register i get the this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Mason'' at line 1

its driving me crazy i went line for line and i still i cant find the error, but i will try it out like you say i will get back to ya thanks for the reply/advice.....b rigth bac

kenrbnsn · January 9, 2009

You have the single quotes around the password in the wrong place. Change:

<?php
  $query = "SELECT username, password FROM user_info".
           "WHERE username = '" . $_POST['username'] . "' ".
	   "AND password = (PASSWORD('". $_POST['password'] . "'))";
?>

to

<?php
  $query = "SELECT username, password FROM user_info".
           "WHERE username = '" . $_POST['username'] . "' ".
	   "AND password = '" . PASSWORD($_POST['password']) . "'";
?>

Ken

MasonPrice · January 9, 2009

hhhmmm... ok will try....rigth now b rigth bac.

MasonPrice · January 9, 2009

no dude did not work i still get this dreaded error im really obsessive about it to...lol

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Mason Price'' at line 1

9three · January 9, 2009

Don't use PASSWORD, I always have problems with it. Use php for encrypting passwords. Try this out:

<?php

//rest of your code...

if (isset($_POST['submit'])) {
$username = mysql_real_escape_string($_POST['username']); // Always sanitize your inputs
$password = md5($_POST['password']);

$query = "SELECT username, password 
           FROM user_info 
           WHERE username = '$username' 
           AND password = '$password'";

$result = mysql_query($query) or die(mysql_error());

// rest of your code..

?>

Read up on SQL Injections. It's why you use mysql_real_escape_string.

MD5 is encrypting your POST password provided. If you want extra security you can use SHA and MD5 together.

I cleaned up some of your code too

darkfreaks · January 9, 2009

MYSQLI is more effective than MYSQL functions in PHP and you don't have to use mysql_real_escape_string() to "band-aid" Injection

btherl · January 9, 2009

MasonPrice, we need to see the actual generated query, as well as the error. For example:

echo "About to run $query<br>";
$result = mysql_query($query)		
     or die(mysql_error());

Otherwise it's like working in the dark. I suspect the issue is that user_info is running into WHERE, forming one token.

MasonPrice · January 10, 2009

same error different day:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Mason Price'' at line 1

this is when i try to register im clueless

when i register its sappose to take me to this page:

personal page.php

<title>Untitled Document</title>

</head>

<body>

<?php

include "auth_user.inc.php";

include "conn.inc.php";

?>

</body>

</html>

<html>

<head>

<title>hello</title>

</head>

<body>

<h1>Welcome to your personal information area</h1>

Here you can update your personal information,

or delete your account.

Your information as you currently have it is shown below:

<a href="index.php">Click here</a> to return to the home page

<?php

$query = "SELECT * FROM user_info " .

"WHERE username = '". $_SESSION['user_logged'] . "' " .

"AND password = (PASSWORD('".

$_SESSION['user_password'] . "'))";

$result = mysql_query($query)

or die(mysql_error());

$row = mysql_fetch_array($result);

?>

First Name: <?php echo $row['first_name']; ?>

Last Name: <?php echo $row['last_name']; ?>

City: <?php echo $row['city']; ?>

State: <?php echo $row['state']; ?>

Mail: <?php echo $row['mail']; ?>

Hoddies/Interests: <?php echo $row['hobbies']; ?>

<a href="update_account.php">Update Account</a> |

<a href="delete_account.php">Delete Account</a>

</body>

</html>

xtopolis · January 10, 2009

To make it easier to see and understand, change your query section in the middle to this:

  $un = $_SESSION['user_logged'];
  $pw = $_SESSION['user_password'];
$query = "SELECT * FROM user_info WHERE username = '$un' AND password = PASSWORD('$pw')";
echo "<b>$query</b>";
$result = mysql_query($query)
  or die(mysql_error());

Then copy and the bold query statement that it shows above the mysql error and post it here.

I have a feeling that some values aren't getting escaped or something.

bdmovies · January 10, 2009

MYSQLI is more effective than MYSQL functions in PHP and you don't have to use mysql_real_escape_string() to "band-aid" Injection

Why is MySQLI more effective?

MasonPrice · January 11, 2009

i have been troubleshooting this script all weekend i even have my brother trying to help me figure out the problem to no avail i have tried all of the suggestions posted here and still stuck.... ???

this is the ERROR:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Mason'' at line 1

can this be the problem:

CREATE TABLE user_info (

email varchar (50) NOT NULL,

username varchar (50) NOT NULL,

password varchar (255) NOT NULL,

first_name varchar (50) NOT NULL,

last_name varchar(50) NOT NULL,

city varchar(50) default NULL,

state varchar(50) default NULL,

hobbies varchar(255) default NULL,

id int(10) NOT NULL default '0'

);

i have posted the whole entire script on here can someone please run it and tell me if they get the same result...please and thank you in advance

xtopolis · January 11, 2009

You should post the query that has that error, not the $sql = , but the actual echo $sql version of it. It's most likely an error in your input because no where in your scripts do you mysql_real_escape_string to your post values.

The mysql error is telling you that where Mason appears actually appears as Mason' and it terminates the quote early. You should check your database to see if there is an unescaped quote in there if it made it that far. Then you should go back and escape all string values from your forms.

kenrbnsn · January 12, 2009

When troubleshooting problems like this, I would do

<?php
$result = mysql_query($query)  or die("Problem with the query <span style='color:red'>$query</span> in line: " . __LINE__ . "<br>" . mysql_error());
?>

Instead of

<?php
$result = mysql_query($query)
  or die(mysql_error());
?>

Since my version would give you much more information.

Ken

MasonPrice · January 13, 2009

ok i will surelly do that im gonna run tru the script again for the 100 time ...

tanx for the advice

Sign In

loggin help please!!! You have an error in your SQL syntax;

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information