mysql_real_escape_string() not escaping single quotes

[glen-rogers]

Hi, I'm using mysql_real_escape_string(), but its not escaping single quote (it's escaping double quotes ok)

 

Is there anything I can do/?

 

Thanks

[louie35]

http://stackoverflow.com/questions/1200972/mysql-real-escape-string-and-single-quote

 

have a look at the options here and use best one that suit you

[glen-rogers]

Hi, its not doing anything with the single quote. If my form data has any single quote then I get a sql error! If my form data has double quotes and no single quotes then it gets added to mysql ok!

[louie35]

You have 2 options here:

strip them off using preg_replace
 

$field_name = preg_replace("#\'#","",$_POST['field_name']);

or convert them to html characters

$field_name = htmlspecialchars($_POST['field_name']);

[mac_gyver]

you are probably not using the output from the mysql_real_escape_string function. post your code to get help with it.

[glen-rogers]

if (!empty($_REQUEST['atitle'])){
    $title = $_REQUEST['atitle'];
}
else{
  $title = NULL;
}
if (!empty($_REQUEST['acontent'])){
    $content = $_REQUEST['acontent'];
}
else{
   $content = NULL;
   echo "<span class='text'><p>Enter content for the News Item</p></span>";
}
$title = mysql_real_escape_string(stripslashes($_POST['$title']));
$content = mysql_real_escape_string(stripslashes($_POST['$content']));
				
if ($title && $content){	
$query = "INSERT INTO projects VALUES (NULL, '$title', '$content', '$remote_file', 
'$remote_file1', '$remote_file2')";
$result = mysql_query($query);
if(!$result){
   $error = 'An error occured: '. mysql_error().'<br />';
   $error.= 'Query was: '.$query;
   echo $error;
   die($message);
}

This is my code

[mac_gyver]

what error do you get?

[glen-rogers]

An error occured: 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 Projects', 'Every month, STU does a "theme" that begins with the same letter o' at line 1

Query was: INSERT INTO projects VALUES (NULL, 'Stu's Projects', 'Every month, STU does a "theme" that begins with the same letter of the corresponding month. This month happens to be "JOLLY ROGER JUNE"... which is all things pirate based. This is primarily ALL images that STU would like to tattoo, at a reduced rate. Posts go on facebook regularly during the month and is all on a "first come first served" basis. Keep an eye out on the themes for the months ahead. There IS a full years listing on STU's facebook page.', '', '', '')

 

I get the above error..................

[louie35]

First I don't get "atitle" and "acontent" in your code

 

Second use this function and see if it works better:

function cleanStr($str){
	$str = trim($str);
	if($str == "") return;
	
	$str = stripslashes($str);//STRIP \ slashes
	if (function_exists(mysqli_real_escape_string)){
		$str = mysqli_real_escape_string($str);
	}else{
		$str = mysql_real_escape_string($str);
	}
	//CONVERT TO HTML
	$str = htmlspecialchars($str);
	//LAST CLEAN UP
	$str = preg_replace("#\'#","",$str);
	return $str;		
}

usage:

$error = array();
$title = !empty($_POST['title']) ? cleanStr($_POST['title']) : "";
$content= !empty($_POST['content']) ? cleanStr($_POST['content']) : "your text here";

if($title == ""){
    $error[] = "Title required";
}

if(count($_POST)>0 && !empty($error)){
    foreach($error as $x=>$y){
        echo $y."<br />";
    }
}else{
    //EXECUTE THE INSERT CODE HERE....
}

Let me know how it's working for you

[mac_gyver]

  On 6/14/2013 at 3:05 PM, glen-rogers said:

I get the above error..................

about the only way the posted code could produce that error is if the ' character is actually a multi-byte encoded character. So, either that's not the actual code that is running or the character encoding isn't being taken into account by the mysql_real_escape_string function.

 

are you sure about the posted code? could you have a version without the mysql_real_escape_string in it that is actually what is running on your server?

 

what's the character encoding defined for your database table? are you specifically selecting that character encoding after your open a database connection in your code?

[glen-rogers]

Thanks for all your help guys, I feel like a complete moron now. I was never uploading the file to the server! I though I was working in localhost, but I had the actual site open instead! I'm a numpty.........................