Page 1 of 2

Trying to use the character '

Posted: Fri May 11, 2007 6:38 am
by aknight
I have a web application that up until a server moved, worked well. Now, anytime the form contains a ' character we get an "invlalid character" error.

Research has shown that we need to be looking at mysql_real_escape_string. After several hours of trying, we still can not figure out what to do with mysql_real_escape_string to make this work.

The databse connection info is kept in a separate file (func.inc) and the pages are php.

Any guidance would be much appreciated.

Posted: Fri May 11, 2007 8:24 am
by Grim...
I assume

Code: Select all

$query = "INSERT INTO table(fielda, fieldb) VALUES('".mysql_real_escape_string($valuea)."', '".mysql_real_escape_string($valueb)."');";
didn't work?

Posted: Fri May 11, 2007 11:44 am
by Kieran Huggins
could have to do with the character encoding of the form - try manually sending a UTF-8 header when you serve the page with the form on it and see if the problem goes away.

Code: Select all

header('Content-Type: text/html; charset=utf-8');
and/or

Code: Select all

<?xml version="1.0" encoding="utf-8" ?>
and/or

Code: Select all

<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />

Posted: Fri May 11, 2007 12:07 pm
by aknight
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />

did the trick, thank you very much.

Allan

Posted: Mon May 14, 2007 8:33 am
by aknight
Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Oh boy, different page, same problem, this time <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />  does not fix it.

I made sure that all PHP pages now have <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />.

The PHP code looks like:

Code: Select all

<?
require "func.inc";

$con=connectdb();
if(!mysql_select_db($db_name,$con)) echo "can not open db";


if($from=="edit"){
	$current=getdate();
	$startdate=$current['year']."-".$current['mon']."-".$current['mday'];
    $title= str_replace("'","''",$title);
    $letter= str_replace("'","''",$letter);
   
	$sql="update letters set title='".$title."', letter='".$letter."', createdate='".$startdate."' where lid=".$id;
}
else
{
	$current=getdate();
	$startdate=$current['year']."-".$current['mon']."-".$current['mday'];
	$title= str_replace("'","''",$title);
    $letter= nl2br(str_replace("'","''",$letter));

	$sql="insert into letters (title, letter,createdate) values('".$title."','".$letter."','".$startdate."')";

}
 

mysql_query($sql,$con) or die("invalid query");



?>
Do I need mysql_real_escape_string somewhere in here?

Again, thank you.

Allan


Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon May 14, 2007 6:22 pm
by RobertGonzalez
Not that it'll help much, but can you try using long opening tags (<?php) instead of short tags (<?).

Posted: Tue May 15, 2007 10:17 am
by aknight
Everah wrote:Not that it'll help much, but can you try using long opening tags (<?php) instead of short tags (<?).
You are right, that did not help either.

Any other ideas would be greatly appreciated.

Allan

Posted: Tue May 15, 2007 10:21 am
by RobertGonzalez
Why are you adding a second single quote to the strings that are being inserted into your database? Also, where exactly is the error being thrown and what is exactly is the error report? Are there any other errors in the error logs of your web server?

Posted: Tue May 15, 2007 10:33 am
by CoderGoblin

Code: Select all

$title= mysql_real_escape_string($title);
    $letter= mysql_real_escape_string($letter);
for debugging you may wanto to echo the sql and copy/paste directly into MySQL without using PHP.

Posted: Tue May 15, 2007 1:17 pm
by aknight
Everah wrote:Why are you adding a second single quote to the strings that are being inserted into your database? Also, where exactly is the error being thrown and what is exactly is the error report? Are there any other errors in the error logs of your web server?
We are seeing the error form the page, being invalid query.

The log file has nothing in the way of errors.

As for the quotes, I am afraid I do not know. I did not write this code, the person who did no longer works here, and it has run fine on our last servers. We did not have an issue until we moved the code to the new servers.

Thanks again.

Allan

Posted: Tue May 15, 2007 1:19 pm
by aknight
CoderGoblin wrote:

Code: Select all

$title= mysql_real_escape_string($title);
    $letter= mysql_real_escape_string($letter);
for debugging you may wanto to echo the sql and copy/paste directly into MySQL without using PHP.
Sorry for being stupid, but where should this go?

I tried under the require statement and then replacing str_replace but neither worked (although replacing the str_replace stopeed the error, nothing got posted.

Allan

Posted: Tue May 15, 2007 1:25 pm
by RobertGonzalez
On the pages that you are getting the invalid query error, add this line after the query...

Code: Select all

<?php
echo $sql;
?>
This will show you what your database is seeing. Please post a few of the queries that you giving you errors. I suspect there is something going on with all that string manipulation.

Posted: Tue May 15, 2007 4:08 pm
by aknight
Shows:

insert into letters (title, letter,createdate) values('Test','Test\''s','2007-5-15')invalid query

Posted: Tue May 15, 2007 4:48 pm
by RobertGonzalez
It looks like magic_quotes are on. This should work if that is the case:

Code: Select all

<?php
$current = getdate();
$startdate = $current['year'] . '-' . $current['mon'] . '-' . $current['mday'];
if ($from=="edit") {
    $sql = "update letters set title='" . mysql_real_escape_string($title) . "', letter='" .  mysql_real_escape_string($letter) . "', createdate='$startdate' where lid=$id";
}
else
{
    $letter = nl2br($letter);

    $sql = "insert into letters (title, letter, createdate) values ('" . mysql_real_escape_string($title) . "','" . mysql_real_escape_string($letter) . "','$startdate')";
}
echo $sql;
?>

Posted: Wed May 16, 2007 6:58 am
by aknight
Everah wrote:It looks like magic_quotes are on. This should work if that is the case:

Code: Select all

<?php
$current = getdate();
$startdate = $current['year'] . '-' . $current['mon'] . '-' . $current['mday'];
if ($from=="edit") {
    $sql = "update letters set title='" . mysql_real_escape_string($title) . "', letter='" .  mysql_real_escape_string($letter) . "', createdate='$startdate' where lid=$id";
}
else
{
    $letter = nl2br($letter);

    $sql = "insert into letters (title, letter, createdate) values ('" . mysql_real_escape_string($title) . "','" . mysql_real_escape_string($letter) . "','$startdate')";
}
echo $sql;
?>
Thank you so much, that worked.

Allan