Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

The Great Escape (character) 1

Status
Not open for further replies.
EvilAsh

EvilAsh

Technical User
Oct 22, 2005
56
0
0
GB
Please can anyone help me? I have been pulling out what is left of my hair for two days on this!

I have built a cms for a website that allows the client to add new journal entries using php/mysql.

It worked absolutely fine in my test environment (PHP4.1.1) however, as soon as I put it into production (PHP5.1.2) it all went horribly wrong.

As you may have predicted from the post's title, the use of single or double quotes in entries is causing the insert/update queries to fail midway.

Now, if it was purely text I would use a function like:

Code: 
$string = (htmlspecialchars(stripslashes($string), ENT_QUOTES));

to clean the string up pre query.

However, many of the exisitng entries (and also new ones) contain HTML tags for images, links etc.

The only workaround I can see is using the above function in the CMS prior to adding to/updating the DB and then using another function to "de-tag" on the pages where the data is to be displayed, such as:

Code: 
$string = str_replace ( ''', '\'', $string ); etc

This will require me to change a number of pages and seems like a lot of hard work :)

Is there an easier way around it?

The update code is shown below for info:

Code: 
mysql_connect("$host","$user","$password"); 
mysql_select_db("$database"); 
$n_o_t =    $_POST['n_o_t'];
$post_date =    $_POST['post_date'];
$post_content =    $_POST['post_content'];
$post_title =    $_POST['post_title'];


$sql="UPDATE journal SET post_date='$post_date',post_content='$post_content',post_title='$post_title' WHERE ID ='$id'"; 
//confirm done
$result = mysql_query($sql); }

Thanks for reading.
 
Sort by date Sort by votes
Thanks for the response

I have tried mysql_escape_string, but again it adds slashes to the DB entry which means I have to add a striplashes command when displaying the data.

I was kinda hoping there was a simple way around it pre insert to the database. PHPmyadmin seems to have overcome the problem - if it comes to it I will have to try and decypher their source code!
 
Upvote 0 Downvote
Yes, mysql_escape_string() adds backslashes to a string. But when you insert that string into a MySQL table, MySQL interpolates the strings. After that, those escape characters are no longer there.

For example, this script:

Code: 
<?php
$dbh = mysql_connect ('localhost', 'test', 'test');
mysql_select_db ('test', $dbh);

$string = <<< EOS
		"Well, now that we have seen each other," said the Unicorn, "if you believe in me, I'll believe in you."
EOS;

print '<html><body><pre>';

print "Original string:\r\n" . $string . "\r\n\r\n";

$string = mysql_escape_string($string);

print "Modified string before insert:\r\n" . $string . "\r\n\r\n";

$query = "INSERT INTO test_table (the_string) values ('" . $string . "')";

$result = mysql_query ($query, $dbh);

if ($result !== FALSE)
{
	$record = mysql_insert_id($dbh);
	
	$query = "SELECT the_string FROM test_table WHERE pkid = " . $record;
	
	$result = mysql_query ($query, $dbh);

	if ($result !== FALSE)
	{
		list ($the_string) = mysql_fetch_array($result);
		
		print "Modified string after insert:\r\n" . $the_string;
	}
}

print '</pre></body></html>';

?>

Outputs:
[tt]Original string:
"Well, now that we have seen each other," said the Unicorn, "if you believe in me, I'll believe in you."

Modified string before insert:
\"Well, now that we have seen each other,\" said the Unicorn, \"if you believe in me, I\'ll believe in you.\"

Modified string after insert:
"Well, now that we have seen each other," said the Unicorn, "if you believe in me, I'll believe in you."[/tt]



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
Thank you, that works fine in dev - I will test it in production tomorrow. You may have saved me a heap of work!

One question though, whats is
Code: 
<<< EOS
? Is it Perl?
 
Upvote 0 Downvote
It's called "heredoc" and it's a construct perl, PHP and a lot of other languages share. When you're going complicated things like nested quotes, it can uncomplicate your code quite a bit.

PHP online manual reference to heredoc
Wikipedia article on the use of heredoc in various languages


As a general security procedure, you should take all user input that will be inserted into a database and run it through mysql_escape_string() if for no other reason than to reduce the chances of an SQL injection.



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
It works a treat. Many, many thanks!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

PeDa
  • Locked
  • Question
POST Content-lLength of ... bytes exceeds the limit of 8388608 bytes in Unknown on line 0
Replies
4
Views
106
feherke
feherke
PeDa
  • Locked
  • Question
unwanted spaces inserted into the middle of php-generated html email body 1
Replies
2
Views
57
PeDa
PeDa
PeDa
  • Locked
  • Question
Report invalid characters in string 1
Replies
2
Views
58
PeDa
PeDa
virtualtom
  • Locked
  • Question
Can ankyone help me extract the data that passes thru this form?
Replies
1
Views
55
spamjim
spamjim
uniopp
  • Locked
  • Question
Japanese Character
Replies
3
Views
49
uniopp
uniopp

Part and Inventory Search

Sponsor

Back
Top