[RESOLVED] Problem updating database when form input contains Apostrophe
Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: [RESOLVED] Problem updating database when form input contains Apostrophe

  1. #1
    Junior Member
    Join Date
    Oct 2010
    Posts
    18

    resolved [RESOLVED] Problem updating database when form input contains Apostrophe

    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:
    Code:
    <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:
    Code:
    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:
    Code:
    $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:

    Code:
    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

  2. #2
    Acceptable PHPist
    Join Date
    Jul 2009
    Location
    Manchester, UK
    Posts
    202
    Can't you just...

    PHP Code:
    $newmaindesc str_replace("'","\\'",$_POST['newmaindesc']); 
    ?

  3. #3
    Junior Member
    Join Date
    Oct 2010
    Posts
    18
    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

  4. #4
    Yes, I can. Wait, what?
    Join Date
    Dec 2006
    Location
    Dallas, TEXAS!
    Posts
    300
    You need to have already established a connection to the MySQL server before you can use mysql_real_escape_string().
    When I die, I hope to die like my grandmother did: peacefully, in my sleep.

    Not panic-stricken, screaming in terror like the passengers in her car . . .

  5. #5
    Acceptable PHPist
    Join Date
    Jul 2009
    Location
    Manchester, UK
    Posts
    202
    With mysql_real_escape_string(), have you already connected to the DB before you call it?

    it needs to go
    PHP Code:
    mysql_connect($host,$user,$pass);
    mysql_real_escape_string($str); 
    not
    PHP Code:
    mysql_real_escape_string($str);
    mysql_connect($host,$user,$pass); 
    ...since it uses the connection in the escape process.

  6. #6
    Junior Member
    Join Date
    Oct 2010
    Posts
    18
    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

  7. #7
    Senior Member djjjozsi's Avatar
    Join Date
    Oct 2008
    Location
    Hungary
    Posts
    2,038
    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.
    Last edited by djjjozsi; 01-21-2011 at 06:37 PM.

  8. #8
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,409
    @rooks: 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?

  9. #9
    Junior Member
    Join Date
    Oct 2010
    Posts
    18
    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
    Code:
    <?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...

  10. #10
    Senior Member djjjozsi's Avatar
    Join Date
    Oct 2008
    Location
    Hungary
    Posts
    2,038
    this is absolutely wrong. Read my suggestions before.
    You should take this line a bit upper:
    PHP Code:
    require("../property_config.php"); 

  11. #11
    Junior Member
    Join Date
    Oct 2010
    Posts
    18
    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

  12. #12
    Senior Member djjjozsi's Avatar
    Join Date
    Oct 2008
    Location
    Hungary
    Posts
    2,038
    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 Code:
    <?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())
        
    $stringstripslashes($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());
    ?>
    Last edited by djjjozsi; 01-22-2011 at 06:49 AM.

  13. #13
    Junior Member
    Join Date
    Oct 2010
    Posts
    18
    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...

    Code:
    if(isset($_POST['subchanges']))
    and if so do I replace the $string syntax with the $_POST values, eg...

    Code:
    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

  14. #14
    Senior Member djjjozsi's Avatar
    Join Date
    Oct 2008
    Location
    Hungary
    Posts
    2,038
    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.

    PHP Code:
    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.
    PHP Code:
    function mres$string ) {
        if(
    get_magic_quotes_gpc())
        
    $stringstripslashes($string);
        return 
    mysql_real_escape_string($string);

    And how to call this function?
    PHP Code:
    $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.

  15. #15
    Junior Member
    Join Date
    Oct 2010
    Posts
    18
    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:

    Code:
    $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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •