Topic: Error in your SQL syntax - escape string?

[V]

I'm working on a comment form for my site, it has a name, email, and comment.

The variable for the comment textarea is,

$comment=$_POST['comment'];

When I submit words like it's  with single quote I get

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 's up?', '7')' at line 2

so I then used escape strings

$comment=mysql_real_escape_string($_POST['comment']);

and now I get

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'SYSTEM'@'localhost' (using password: NO) in C:\wamp\www\sitetest\commentajax.php on line 10

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\wamp\www\sitetest\commentajax.php on line 10
ds

Do you think this is because I'm using WAMP? I'm not sure what to do..

[Alex]

You need to establish a connection to the server before you can use mysql_real_escape_string().

[PFMaBiSmAd]

From the mysql_real_escape_string() documentation -

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

[V]

Thanks for replying! I'm already connected to the server. Without mysql_real_escape_string() there are no errors unless I use single quotes and the input data is loaded into the mysql tables. Or by server do you mean publish the website on the Internet?

[Alex]

Can you post your entire code? It will help us identify your problem much more easily.

[V]

Can you post your entire code? It will help us identify your problem much more easily.

Yes sir! This is the comments.php

<script type="text/javascript">
$(function() {
$(".submit").click(function() 
{
var name = $("#name").val();
var email = $("#email").val();
var comment = $("#comment").val();
var post_id = $("#post").val(); 
var dataString = 'name='+ name + '&email=' + email + '&comment=' + comment+ '&post_id=' + post_id;
if(name=='' || email=='' || comment=='')
{
alert('Please Give Valid Details');
}
else
{
$("#flash").show();
$("#flash").fadeIn(400).html('<img src="ajax-loader.gif" />Loading Comment...');
$.ajax({
type: "POST",
url: "commentajax.php",
data: dataString,
cache: false,
success: function(html){
$("ol#update").append(html);
$("ol#update li:last").fadeIn("slow");
$("#flash").hide();
}
});
}return false;
}); });
</script>

<ol id="update" class="timeline">

<?php
//$post_id value comes from the POSTS table
$post_id = $_GET['post'];

//1. Create DB connection
$connection = mysql_connect("localhost", "user7", "password7"); 

if (!$connection) {

die("Database connection failed: " .mysql_error());
}


//2. Select a DB to use

$db_select = mysql_select_db("sitetest",$connection);

if (!$db_select) {

die("Database selection failed: " .mysql_error());

}

//3. Category Query

$comments_table = mysql_query("SELECT * FROM comments WHERE post_id='$post_id'", $connection);

if (!$comments_table) {

die("Database query failed: " .mysql_error());

} 

while ($row = mysql_fetch_array($comments_table)) { 


$name=$row['com_name'];
$email=$row['com_email'];
$comment=$row['com_dis'];
$lowercase = strtolower($email);
?>

//Displaying existing or old comments
<li class="box">
<span class="com_name"> <?php echo $name; ?></span> <br />
<?php echo $comment; ?></li></span>
<?php
} //closes loop

//5. close DB conection

if (isset($connection)) { //if there is a $connection variable close DB connection otherwise ignore the function

mysql_close($connection);

}
?>

</ol>
<div id="flash"></div>
<div >
<form action="#" method="post">
<input type="hidden" id="post" value="<?php echo $post_id; ?>"/> 
<input type="text" id="name"/>Name<br />
<input type="text" id="email"/>Email<br />
<textarea id="comment" style="height: 30px; display: inline;"></textarea>
<div id="button_block">
<input type="submit" id="button" class="submit" value=" Share "/>
<input type="submit" id='cancel' value=" cancel" />
</div>
</form>
</div>

then the commentsajax.php

if($_POST)
{

$name=$_POST['name'];
$email=$_POST['email'];
$comment=$_POST['comment'];
$post=$_POST['post_id']; 
$lowercase = strtolower($email);


$connect = mysql_connect("localhost","user7","password7");
if (!$connect)
  {
  die('Could not connect: ' . mysql_error());
  }
  
  mysql_select_db("gisttest", $connect);


$insert_comment="INSERT INTO comments (com_name,com_email,com_dis,post_id)
VALUES ('".$name."', '".$email."', '".$comment."', '".$post."')";
}


if (!mysql_query($insert_comment,$connect))
  {
  die('Error: ' . mysql_error());
  }

mysql_close($connect)

?>

<li class="box">
<?php echo $name;?><br />
<?php echo $comment; ?>
</li>

[V]

Yes sir! This is the comments.php

<script type="text/javascript">
$(function() {
$(".submit").click(function() 
{
var name = $("#name").val();
var email = $("#email").val();
var comment = $("#comment").val();
var post_id = $("#post").val(); 
var dataString = 'name='+ name + '&email=' + email + '&comment=' + comment+ '&post_id=' + post_id;
if(name=='' || email=='' || comment=='')
{
alert('Please Give Valid Details');
}
else
{
$("#flash").show();
$("#flash").fadeIn(400).html('<img src="ajax-loader.gif" />Loading Comment...');
$.ajax({
type: "POST",
url: "commentajax.php",
data: dataString,
cache: false,
success: function(html){
$("ol#update").append(html);
$("ol#update li:last").fadeIn("slow");
$("#flash").hide();
}
});
}return false;
}); });
</script>

<ol id="update" class="timeline">

<?php
//$post_id value comes from the POSTS table
$post_id = $_GET['post'];

//1. Create DB connection
$connection = mysql_connect("localhost", "user7", "password7"); 

if (!$connection) {

die("Database connection failed: " .mysql_error());
}


//2. Select a DB to use

$db_select = mysql_select_db("sitetest",$connection);

if (!$db_select) {

die("Database selection failed: " .mysql_error());

}

//3. Comments Query

$comments_table = mysql_query("SELECT * FROM comments WHERE post_id='$post_id'", $connection);

if (!$comments_table) {

die("Database query failed: " .mysql_error());

} 

while ($row = mysql_fetch_array($comments_table)) { 


$name=$row['com_name'];
$email=$row['com_email'];
$comment=$row['com_dis'];
$lowercase = strtolower($email);
?>

//Displaying existing or old comments
<li class="box">
<span class="com_name"> <?php echo $name; ?></span> <br />
<?php echo $comment; ?></li></span>
<?php
} //closes loop

//5. close DB conection

if (isset($connection)) { //if there is a $connection variable close DB connection otherwise ignore the function

mysql_close($connection);

}
?>

</ol>
<div id="flash"></div>
<div >
<form action="#" method="post">
<input type="hidden" id="post" value="<?php echo $post_id; ?>"/> 
<input type="text" id="name"/>Name<br />
<input type="text" id="email"/>Email<br />
<textarea id="comment" style="height: 30px; display: inline;"></textarea>
<div id="button_block">
<input type="submit" id="button" class="submit" value=" Share "/>
<input type="submit" id='cancel' value=" cancel" />
</div>
</form>
</div>

then the commentsajax.php

if($_POST)
{

$name=$_POST['name'];
$email=$_POST['email'];
$comment=$_POST['comment'];
$post=$_POST['post_id']; 
$lowercase = strtolower($email);


$connect = mysql_connect("localhost","user7","password7");
if (!$connect)
  {
  die('Could not connect: ' . mysql_error());
  }
  
  mysql_select_db("sitetest", $connect);


$insert_comment="INSERT INTO comments (com_name,com_email,com_dis,post_id)
VALUES ('".$name."', '".$email."', '".$comment."', '".$post."')";
}


if (!mysql_query($insert_comment,$connect))
  {
  die('Error: ' . mysql_error());
  }

mysql_close($connect)

?>

<li class="box">
<?php echo $name;?><br />
<?php echo $comment; ?>
</li>

So it successfully inserts and queries the DB but I get those errors with the string escape function.

[DavidAM]

$name=$_POST['name'];
$email=$_POST['email'];
$comment=$_POST['comment'];
$post=$_POST['post_id']; 
$lowercase = strtolower($email);

$connect = mysql_connect("localhost","user7","password7");


At the time that you called mysql_real_escape, you did not have the connection established.  Make the connection first.

AND for your own protection, mysql_real_escape EVERYTHING that comes from the POST variables!!

[V]

Aaah I see, glad it wasn't anything complex Thank you for noticing!! BTW, in my page the slashed \ appear but I see in phpMyAdmin in tables, the slashes don't show. Is that normal?

[fenway]

Can you post your entire code? It will help us identify your problem much more easily.

I totally disagree -- PHP code is for the PHP forum.

[DavidAM]

BTW, in my page the slashed \ appear but I see in phpMyAdmin in tables, the slashes don't show. Is that normal?

Yes and No.

When you call mysql_real_escape_string() it puts backslashes in the string to prevent the field from terminating early.  For example, to set a user's name to "O'Roark"; without the escape the command sent to the database would like something like this:
UPDATE Users SET LastName = 'O'Roark' WHERE ID = 4; and the apostrophe in "O'Roark" ends the quote for the string. The rest of it "Roark'" causes the server to throw an error because it does not know what to do with it. When you escape it, the command looks something like this: UPDATE Users SET LastName = 'O\'Roark' WHERE ID = 4;; which tells the server that the apostrophe is a literal character in the value. The server removes the backslash and inserts the data as you expect. So if you display an escaped string, you will see the backslashes.

If you are going to send the variable to the browser for display or editing after using mysql_real_escape_string(), then you should call stripslashes() on it. echo stripslashes($name);. If you don't, and the user posts the data again, the call to mysql_real_escape_string() will escape the backslash causing it to be inserted into the database.

[V]

DavidAM I totally understand now! Thank you for sharing your knowledge, I hope I'll enlighten people as well one day

@fenway I'm very sorry, I didn't realize this was more of a php issue, next time I will be more aware. thanks

[fenway]

@fenway I'm very sorry, I didn't realize this was more of a php issue, next time I will be more aware. thanks

Basically, it depends on what's causing the error -- if you're getting php errors, it's not mysql's fault.

[V]

@fenway I'm very sorry, I didn't realize this was more of a php issue, next time I will be more aware. thanks

Basically, it depends on what's causing the error -- if you're getting php errors, it's not mysql's fault.

Ah I see. Understood! Thank you for the clarification