Link to home
Start Free TrialLog in
Avatar of nwalker78
nwalker78

asked on

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]

Hi having very frustrating time trying to sort the stated error. on excecution i get hit with:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''accounts' ('username', 'password', 'salt') VALUES ('321','456','4T1ffup28X8bzcQ' at line 1' in D:\wamp\www\ogserver\index.php on line 37
the code that generates this is as follows
<?php

	$sqlHost		=	'localhost';
	$sqlUser		=	'removed';
	$sqlPass		=	'removed';
	$sqlDatabase	=	'ogserverdb';
	
	$connection = new PDO('mysql:host='.$sqlHost.';dbname='.$sqlDatabase.';charset=utf8', $sqlUser, $sqlPass);
	$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
		
	$username		=	null;
	$password		=	null;
	
	if (isset($_GET['user'])) {
		$username		=	$_GET['user'];
	}
	
	if (isset($_GET['pass'])) {
		$password		=	$_GET['pass'];
	}
	
	if ((isset($username)) && (isset($password))) {
		// TODO SQL
		$salt		=	genSalt(40);
		$passHash	=	md5(md5($salt).md5($password));
		
		$statement	=	$connection->prepare("INSERT INTO 'accounts' ('username', 'password', 'salt') VALUES (:myuser,:mypass,:mysalt);");
		$statement->bindParam(":myuser", $username);
		$statement->bindParam(":mypass", $password);
		$statement->bindParam(":mysalt", $salt);
		
		echo $username."<p>";
		echo $password." Hashed: (".$passHash.")<p>";
		echo $salt."<p>";
		
		if($statement->execute()) {
			echo "Hello ".$username." Thank you for your registration";
		} else {
			echo "There was a problem and your registration failed!";
		}

	} else {
		// DISPLAY
		echo '<input type="text" id="user" placeholder="username"/>
		<input type="text" id="pass" placeholder="password"/>
		<button id="button">Register</button>
		
		<script>
			var button = document.getElementById("button");
			button.addEventListener("click", function() {
				var user = document.getElementById("user");
				var pass = document.getElementById("pass");
				if(user.value.length < 3 || pass.value.length < 3){
					alert("Please enter a valid username or password");
				} else {
					window.location = "index.php?user="+user.value+"&pass="+pass.value;
				}
			});
		</script>
		';
	}
	
	
	function genSalt($length)
	{
		$variables		=	'aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ0123456789';
		$charLength		=	strlen($variables);
		$returned		=	'';
		for($i = 0; $i < $length; $i++)
		{
			$returned	.=	$variables[rand(0, ($charLength - 1))];
		}
		return $returned;	
	}
	
?>

Open in new window


apologies for crudity of code, i wanted to get it working in its basic form before i did to much.

any help and advice is much appreciated.
Avatar of Brian Tao
Brian Tao
Flag of Taiwan, Province of China image

remove the ' around the table name accounts.
Avatar of nwalker78
nwalker78

ASKER

tried without the ' around accounts but still getting

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''username', 'password', 'salt') VALUES ('user','pass','vXgHfSUpfwn3ehVIbVGakHF3a' at line 1' in D:\wamp\www\ogserver\index.php on line 37

not much hair left here!!!!!!!!!!
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
many thanks my sanity has been saved!!!!!!!!!!!!!!!!!!!!!!!
Yay!  If you need to use quote marks in SQL queries (like in the case that someone has thoughtlessly named a column with a SQL reserved word) you probably want to try backticks.  On my keyboard they are the lower-case character to the left of the number 1.  They look a lot like single quotes, but have different meaning and effect.

Those SQL messages are only marginally useful at best, aren't they?!
hehehe yes, came across a couple of posts mentioning backticks, what hurt head even more i copied code verbatim from a youtube tute with exception of db info like user/pass ect and even though the code on tute worked "see it run in vid" it still gave grief.