Errrg... SQL Syntax

[ShoeLace1291]

I'm having trouble with my SQL Syntax.  I'm getting an error that says "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 'Prayer' at line 1".  Prayer is part of the username that I put in (Unholy Prayer).  This is my code:

$sql = mysql_query("INSERT INTO members (displayname,email,password,activation_code,is_activated)
			values(".$username.", '$email', '$pass', '$activationcode', '0')") or die("Error: ".mysql_error());

[genericnumber1]

$sql = mysql_query("INSERT INTO members (displayname,email,password,activation_code,is_activated)
			values('$username', '$email', '$pass', '$activationcode', '0')") or die("Error: ".mysql_error());

 

[ShoeLace1291]

I'm still getting the same error.

[genericnumber1]

The error you specified I'm pretty sure I fixed... The only other error there possibly could be is if is_activated is a numeric only field, if so you would want to do 0 and not '0'

[ShoeLace1291]

That didn't work, either.  "is_activated" is a INT field but the error is still coming up(the same error as earlier).

[Barand]

If you define the query first you can echo the content if there is a problem. What does it give?

$query= "INSERT INTO members (displayname,email,password,activation_code,is_activated)
			values('$username', '$email', '$pass', '$activationcode', '0')";

$sql = mysql_query($query) or die("Error: " . mysql_error() . "<p>$query</p>" );

[per1os]

It sounds to me like your data is not being escaped.

 

try this:

 

<?php
$username = mysql_real_escape_string($username);
$query= "INSERT INTO members (displayname,email,password,activation_code,is_activated)
			values('$username', '$email', '$pass', '$activationcode', 0)";

 

And see where that gets you.

[ShoeLace1291]

Its still not working.  This is my entire code if it helps you better.

<?php

require_once('config.php');

    if(!$_POST['register']){

$terms = file_get_contents("docs/terms.txt");

echo "<table align='center' cellspacing='1' cellpadding='1' border='0'>
	   <tr>
		<td align='center' colspan='2'>New User Registration</td>
	   </tr><tr><form action='register.php' method='POST'>
		<td align='right'>Username: </td>
		<td align='left'><input type='text' name='username' size='30'></td>
	   </tr><tr>
		<td align='right'>Email Address: <br><small>This email address will be used to send your password to you.  <br>You will then be able to change your password after activation.</small></td>
                        <td align='left'><input type='text' name='email' size='30'></td>
	   </tr><tr>
		<td align='right'>Confirm Email: </td>
		<td align='left'><input type='text' name='email_conf' size='30'></td>
	   </tr><tr>
	   	<td align='center' colspan='2'>Please read the terms and conditions of the Storm Creations website.  Once you agree to these terms and register, you MUST agree to them or we reserve the right to ban you permanently from the site.<br><br>
	<textarea rows='10' cols='30'>$terms</textarea><br><br>
	<input type='checkbox' name='agree' value='yes'>I agree to the terms and conditions.</td>
	   </tr><tr>
		<td align='center' colspan='2'><input type='submit' value='Register' name='register'></form></td>
	   </tr>
	</table>";
}

if(isset($_POST['register'])){

	$username = $_POST['username'];
	$email = $_POST['email'];
	$email_conf = $_POST['email_conf'];
	$security = $_POST['security'];

	$errors = 0;

	   $countusers = "SELECT * FROM members WHERE displayname = $username";
           $result = mysql_query($countusers) or die("Error: ".mysql_error());
           $inuse = mysql_num_rows($result);
	   
	if($inuse > 0){
		echo "The username you selected is already in use.  Please go back and choose another.";
	} 

	if(empty($_POST['agree'])){
		echo "You must agree to the terms and conditions before you can register.";
	}

	if(!$username){
		echo "You must select a username.  Use your browser's back button to fix this error.";
		$errors = $errors + 1;
	}

	if(!$email){
		echo "You need to give us your email address so we can send you your password.  Use your browser's back button to fix this error.";
		$errors = $errors + 1;
	}

	if(!$email_conf){
		echo "Please confirm your email address.  Use your browser's back button to fix this error.";
		$errors = $errors + 1;
	}

	if(!$security){
		echo "Complete the form by inserting the string of characters you see.  This is to ensure that you are not a bot.  Use your browser's back button to fix this error.";
		$errors = $errors + 1;
	}

	if($email != $email_conf){
		echo "Your email addresses did not match.  Use your browsers back button to fix this error.";
		$errors = $errors + 1;
	}

	if($errors == 0){

	function createpass(){

		$chars = "abcdefghijklmnopqrstuvwxyz0123456789
			srand((double)microtime()*100000)";
			$i = 0;
			$pass = '';

			while($i <= 6){
				$num = rand() % 33;
				$tmp = substr($chars, $num, 1);
				$pass = $pass . $tmp;
				$i++;
			}

		return $pass;
	}

		echo $createpass(); 

			function randomString($length){
    
// Generate random 32 charecter string
    $string = md5(time());

    // Position Limiting
    $highest_startpoint = 32-$length;

    // Take a random starting point in the randomly
    // Generated String, not going any higher then $highest_startpoint
    $activationcode = substr($string,rand(0,$highest_startpoint),$length);

    return $activationcode;

}

		$username = mysql_real_escape_string($username);
$query = mysql_query("INSERT INTO members (displayname,email,password,activation_code,is_activated)
			values('$username', '$email', '$pass', '$activationcode', 0)");

			  $body = "Thank you for registering at Storm Creations.net.  
			  Your password is $pass.  Before you may login, you need to activate your account by visiting the link below:<br>
			  		  		 http://www.stormgaming.net/stormcreations/register.php?act=activate&CODE=$activationcode";
			  mail($email, 'Storm Creations Account Info', $body, 'From: unholyprayer@stormgaming.net');

}

  }
  


?>

[Barand]

It would help better if you post the actual query code being executed

[ShoeLace1291]

If you would look through the code, it's there.  I just thought it could be caused by another part of the script.  It's not that long of a code.

[Barand]

If you define the query first you can echo the content if there is a problem. What does it give?

$query= "INSERT INTO members (displayname,email,password,activation_code,is_activated)
			values('$username', '$email', '$pass', '$activationcode', '0')";

$sql = mysql_query($query) or die("Error: " . mysql_error() . "<p>$query</p>" );

[Barand]

All I need to see is the actual query generated by your code. If you don't want to help me to help you then it's goodbye from me.

[john010117]

sql = mysql_query("INSERT INTO `members` (displayname,email,password,activation_code,is_activated) values ('$username', '$email', '$pass', '$activationcode', '0')") or die("Error: ".mysql_error());

 

Putting backticks might work.

[Barand]

Only if the value inside the backticks contains space or is a reserved word.

[john010117]

Oh, sorry. My bad.

[Barand]

If you are building a query from variables, particularly user-input ones, then yes, put the backticks in, just in case the above conditions apply.

 

eg

 

$query = "SELECT `$a`, `$b`, `$c` FROM `$table`";

[ShoeLace1291]

None of those worked...  :'(

[benjaminbeazy]

have you not read what people have been asking for? what is the output of the query?

 

do this and let us know what the page says....

 

If you define the query first you can echo the content if there is a problem. What does it give?

$query= "INSERT INTO members (displayname,email,password,activation_code,is_activated)
			values('$username', '$email', '$pass', '$activationcode', '0')";

$sql = mysql_query($query) or die("Error: " . mysql_error() . "<p>$query</p>" );

[ShoeLace1291]

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 'Prayer' at line 1

[benjaminbeazy]

just echo the query then, dont try to run it, just echo $query

[ShoeLace1291]

It's still not working.  I get the same error.

[benjaminbeazy]

can you post your most up to date code, and a link if possible?

[JakeTheSnake3.0]

In the text you provided, you didn't have a space after the values part...

 

$query = "INSERT INTO members (displayname, email, password, activation_code, is_activated) VALUES ('$username', '$email', '$pass', '$activationcode', '0')";