Trying to use the character '

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

aknight
Forum Newbie
Posts: 8
Joined: Fri May 11, 2007 6:35 am

Trying to use the character '

Post 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.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post 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?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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" />
aknight
Forum Newbie
Posts: 8
Joined: Fri May 11, 2007 6:35 am

Post by aknight »

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

did the trick, thank you very much.

Allan
aknight
Forum Newbie
Posts: 8
Joined: Fri May 11, 2007 6:35 am

Post 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]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Not that it'll help much, but can you try using long opening tags (<?php) instead of short tags (<?).
aknight
Forum Newbie
Posts: 8
Joined: Fri May 11, 2007 6:35 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
aknight
Forum Newbie
Posts: 8
Joined: Fri May 11, 2007 6:35 am

Post 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
aknight
Forum Newbie
Posts: 8
Joined: Fri May 11, 2007 6:35 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
aknight
Forum Newbie
Posts: 8
Joined: Fri May 11, 2007 6:35 am

Post by aknight »

Shows:

insert into letters (title, letter,createdate) values('Test','Test\''s','2007-5-15')invalid query
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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;
?>
aknight
Forum Newbie
Posts: 8
Joined: Fri May 11, 2007 6:35 am

Post 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
Post Reply