[RESOLVED] Problem updating database when form input contains Apostrophe

[rooks]

Hi all,

I have created a form which pulls info from a mysql db, allowing user to edit the details by overtyping but I am tearing my hair out with a problem I am getting when the user enters an apostrophe. I will try to illustrate with a few select lines of code first.

Form page code excerpt:

<form method="post" action="admin_confirm_edit.php"> 
<textarea name="newmaindesc" cols="70" rows="10"><?php echo $row[6]; ?></textarea>
<input name="subchanges" type="submit" value="submit changes" />

admin_confirm_edit.php page:

if(isset($_POST['subchanges'])) 
	{ $newmaindesc = str_replace("'","''",$_POST['newmaindesc']);

//update the db
	$sql = "UPDATE table1 SET maindesc = '$newmaindesc',	

The above actually works in my test environment (ie the apostrophes are accepted & can be viewed afterwards) but on my website with same code, same db type etc the update does not work.

Having scoured various forums I thought the answer might be to use this instead of the str_replace:

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

but not only did this not work in either or test or live but it seemed to delete all the data that was in my records! Here is the error message I received:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'nottswe1'@'localhost' (using password: NO) in /home/nottswe1/public_html/property_portfolio_demo/admin_confirm_edit.php on line 50

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/nottswe1/public_html/property_portfolio_demo/admin_confirm_edit.php on line 50

(line 50 is the one that contains the mysql_real_escape_string line). Can someone please please point me in the right direction as to finding a solution that will allow users to enter apostrophes (and for the apostrophes to be displayed properly afterwards).

Many thanks in advance

Col

ps I can post all the code if this helps

[DaveRandom]

Can't you just...

$newmaindesc = str_replace("'","\\'",$_POST['newmaindesc']);

?

[rooks]

thanks for suggestion Dave.

just tried that. Like my first solution it worked in test but in my live db it does not update the record (or provide an error message). The live db is fine when I update with a normal character so i don't think it's broken in any way.

Col

[Pikachu2000]

You need to have already established a connection to the MySQL server before you can use mysql_real_escape_string().

[DaveRandom]

With mysql_real_escape_string(), have you already connected to the DB before you call it?

it needs to go

mysql_connect($host,$user,$pass);
mysql_real_escape_string($str);

not

mysql_real_escape_string($str);
mysql_connect($host,$user,$pass);

...since it uses the connection in the escape process.

[rooks]

Thanks for the suggestions guys, unfortunately everything is just being rejected when I put the connection in first:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'nottswe1'@'localhost' (using password: NO) in /home/nottswe1/public_html/property_portfolio_demo/admin_confirm_edit.php on line 58

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/nottswe1/public_html/property_portfolio_demo/admin_confirm_edit.php on line 58

I think I may have to go back to the drawing board with this one, since obviously I am getting completely different results to what you suggest. All my connections have been fine prior to playing around with this code and I havent changed anything as far as I'm aware

Col

[djjjozsi]

you should not use str_replace in this case.
To escape the apostrofe for your SQL update you need to handle magic qoutes first,
then apply mysql_real_escape_string() on the user inputs.
Other metioned suggestions before are bad techniques.

Always ask with enough source code.

-Echo your user inputs ( print_r($_POST ) ; )
-Echo your SQL string before run.
-Handle the mysql error: mysql_error();
-use stripslashes() if magic qoutes is enabled on your server.

[bradgrafelman]

@: Can you show us more of the code you're using? Specifically, can you show us how you connect to the DB before you try to escape the input?

[rooks]

ok guys, thanks for all the help so far.
The following code accepts apostrophes in test but not in live. In live it only accepts the update if no apostrophes are present:

Let me know if you need the first part where the page populates the form prior to edit. At this point the changes have already been made & submitted

<?php

// if submit button  has been pressed to make a record change then...
if(isset($_POST['subchanges'])) 
	{ 	
	// we need to feed all possible fields which could have changed
	//nb propid, beds, type, loc not editable
	$newpropid = $_POST['newpropid'];
	$newprice = $_POST['newprice'];
	$newbeds = $_POST['newbeds'];
	$newtype = $_POST['newtype'];
	$newloc = $_POST['newloc'];
$newmaindesc = str_replace("'","''",$_POST['newmaindesc']);
$newroom1hdr = str_replace("'","''",$_POST['newroom1hdr']);
$newroom1det = str_replace("'","''",$_POST['newroom1det']);
$newroom2hdr = str_replace("'","''",$_POST['newroom2hdr']);
$newroom2det = str_replace("'","''",$_POST['newroom2det']);
$newroom3hdr = str_replace("'","''",$_POST['newroom3hdr']);
$newroom3det = str_replace("'","''",$_POST['newroom3det']);
$newroom4hdr = str_replace("'","''",$_POST['newroom4hdr']);
$newroom4det = str_replace("'","''",$_POST['newroom4det']);
$newroom5hdr = str_replace("'","''",$_POST['newroom5hdr']);
$newroom5det = str_replace("'","''",$_POST['newroom5det']);

require("../property_config.php");		
		if ($mysqli === false) {
			die("Error - could not connect. " . mysqli_connect_error());
								}

//update the db
$sql = 
"UPDATE table1 SET 

price = '$newprice', 

maindesc = '$newmaindesc',
hdr1 = '$newroom1hdr',
desc1 = '$newroom1det',
hdr2 = '$newroom2hdr',
desc2 = '$newroom2det',
hdr3 = '$newroom3hdr',
desc3 = '$newroom3det',
hdr4 = '$newroom4hdr',
desc4 = '$newroom4det',
hdr5 = '$newroom5hdr',
desc5 = '$newroom5det'	

WHERE id = $newpropid"	;
	if ($mysqli->query($sql) === true) {
		?><div class="msg"><?php	
		echo 'Record id ' .  $newpropid .  ' updated';
		?></div><?php
		$imagepath = substr($row[4], 0, 17); 

		//if update has worked, display the record

		$sql = 
		"SELECT table1.id, table1.price, table1.beds, table1.type, table1.mainpic, table1.location, table1.maindesc, table1.hdr1,
		table1.desc1, table1.hdr2, table1.desc2, table1.hdr3, table1.desc3, table1.hdr4, table1.desc4, table1.hdr5, table1.desc5
		FROM table1 WHERE ('$newpropid' = table1.id)";

			if ($result = $mysqli->query($sql)) {
				if ($result->num_rows > 0) {
					while($row = $result->fetch_array()) {
//display the newly edited record...

[djjjozsi]

this is absolutely wrong. Read my suggestions before.
You should take this line a bit upper:

require("../property_config.php");

[rooks]

djjozsi I did read your suggetion (thankyou) but as I am a begginer I nead to read up first on magic quotes etc. I posted the code for the benefit of brad (who requested i do so) and others who may be able to spot what is wrong. I will try your suggestion but when I moved the require statement earlier (albeit with a different solution to your's) this caused me more problems than before

[djjjozsi]

You're using mysqli classes to connect to the database. Then you should not use mysql_real_escape_string()

Why don't you try the simplest version mysql_real_escape_string() ?

<?php 
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

function mres( $string ) {
	if(get_magic_quotes_gpc())
	$string= stripslashes($string);
	return mysql_real_escape_string($string);
}

$field1=mres($_POST["field1"]);
$field2=mres($_POST["field2"]);
$id=intval($_POST["id"]);
$update="update tablaname SET field1='$field1' , field2='$field2' WHERE id=$id";

mysql_query($update) or die(mysql_error());
?>

[rooks]

thanks djjozi, I will try what you have suggested.

could you just clarify one thing please:

should your initial mysql_connect appear after the submit button check, ie after...

if(isset($_POST['subchanges'])) 

and if so do I replace the $string syntax with the $_POST values, eg...

function mres($_POST['newmaindesc']) {

or does this code need to go into the other php doc prior to posting the form with the changes?

Thanks a lot,

Col

[djjjozsi]

should your initial mysql_connect appear after the submit button check, ie after...

Connect to a database is a good idea to put on the very beggining of your program. This is the only one stuff you're using in your entire site.

function mres($_POST['newmaindesc']) {

or does this code need to go into the other php doc prior to posting the form with the changes?

function mres () is a function declaration. You should not edit this declaration.

function mres( $string ) {
    if(get_magic_quotes_gpc())
    $string= stripslashes($string);
    return mysql_real_escape_string($string);
} 

And how to call this function?

$field1=mres($_POST["field1"]);
$field2=mres($_POST["field2"]); 

The user inputs then escaped properly depending on the server's magic qoutes gpc setting.
For the better explanation, you should read about user defined functions.

[rooks]

thanks so much djjjozsi, I have managed to get it working to a degree after following your instructions.

my only problem now is that I had coded all this is mysqli so the part where I need to do something else after this no longer works. Is there a mysql equivalent for the row check:

$sql = 
"SELECT table1.id, table1.price ... 
FROM table1 WHERE ('$newpropid' = table1.id)";

if ($result = $mysqli->query($sql)) {
if ($result->num_rows > 0) {
while($row = $result->fetch_array()) {

anyway I will see if I can work this out.

Many thanks everyone once again

Col

[rooks]

all working now. thankyou all !!