www.webdeveloper.com
Results 1 to 9 of 9

Thread: Update using mysql_result and special characters

  1. #1
    Join Date
    Oct 2006
    Location
    Ables Springs, Texas
    Posts
    140

    Update using mysql_result and special characters

    When attempting to update a database file with a field containing the apostrophe special character ('), I have to manually escape that character in order to successfully pass the entry to the action file. Is there a way, similar to mysql_real_escape_string, to do this without using the escape \?

    For example, I use an update.html to enter the unique parameter
    Code:
    	
      <form method="post" action="update.php">
      <fieldset>
    	<label>Program Name</label><input type="text" name="progname" size="40">
    	<input type="submit" value="Submit" style="margin:0 1em"/>
     	<input type="reset" value="Reset" style="margin-left:1em"/>
      </fieldset>
      </form>
    The update.php file receives the parameter and posts the complete record, then passes changes to updated.php
    Code:
      $query="SELECT * FROM programs WHERE progname='$progname'";
      $result=mysql_query($query)or die(mysql_error());
    
      $num=mysql_numrows($result); 
      $i=0;
      mysql_close();
    
      while ($i < $num) {
      $progname=mysql_result($result,$i,"progname");
    If the "progname" entry contains an apostrophe (ex: Leanin' Tree), and is entered in the .html file without the escape character, when submitted it returns an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Tree'' at line 1

    The "progname" entry in the example above must be entered with the escape character (Leanin\' Tree) in order to successfully pass to the update.php file. Same applies when the update.php file passes the data to updated.php file. Even though the update.php file displays the progname correctly (Leanin' Tree), I must manually add the escape character again before submitting to updated.php.

    Of course, updated.php has no problem passing to the database, since it uses
    Code:
    $progname=mysql_real_escape_string($_POST['progname']);
    Keep in mind, I am not attempting to change the "progname" entry, just other data associated with that entry.

    Is it possible to perform the update on such entries without having to manually enter the escape character, just like when entering the "progname" for the first time using
    Code:
    $progname=mysql_real_escape_string($_POST['progname']);
    and
    Code:
    $query = "INSERT INTO programs 
    	VALUES (
      	'$progname',
    That is accomplished without the need to enter the escape character.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    It has to be escaped, or the SQL will not be parsed correctly by the DBMS. Unless I'm not really understanding your question, the only two choices are to continue using an escaping function for the DBMS being used (such as mysql_real_escape_string() for MySQL), or use prepared statements with bound parameters as you can with the PDO or MySQLi PHP extensions. (And since the old MySQL extension is now deprecated in PHP, you really shouldn't be using it any more anyway.)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Oct 2006
    Location
    Ables Springs, Texas
    Posts
    140
    What's confusing to me is, I don't have to use the escape character when searching or updating through phpMyAdmin, but I do need to use it with my external search and update code.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    That's because phpMyAdmin is a PHP program, and you can be pretty darned sure they are using one of the two methods I described above.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Oct 2006
    Location
    Ables Springs, Texas
    Posts
    140
    Then I obviously don't understand what you're trying to tell me. Why does their code work and mine doesn't (without the escape character)? I don't need reference to what they are doing, I need examples.

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    What I'm saying is that phpMyAdmin (which is not part of MySQL, but a stand-alone tool) is a PHP program that provides you HTML forms into which you can enter data to be inserted or updated (amongst the many other things it provides). You then submit it just like any other HTML form, which is then sent to a PHP script that takes the data submitted, processes it, escapes values that need to be escaped, then submits it as a SQL query to the database. Therefore, somewhere within the PHP code that is phpMyAdmin, they are either directly escaping inputs as needed, or (more likely with recent versions, I hope) using prepared statements and bound parameters -- the same as all us other PHP/MySQL (or PostgeSQL, etc.) developers do all the time.

    There are database abstraction layers you could use that handle the data escaping themselves so you wouldn't have to, but ultimately, somewhere along the line, those single quotes and a few other special characters have to be escaped before the query actually gets submitted to the MySQL server.

    FYI, here's an example of how I might process an insert into a MySQL DB using PHP's PDO extension. The goal here is not to write the least code, but the most robust and easy-to-maintain code.
    PHP Code:
    try {
        
    $pdo = new PDO('mysql:host=localhost;dbname=test'$dbUser$dbPass);
        
    $pdo->setAttribute(PDO::ERRMODE_EXCEPTION);
        
    $sql "
            INSERT INTO table_x (
                col_1,
                col_2
            ) VALUES (
                :val_1,
                :val_2
            )
        "
    ;
        
    $stmt $pdo->prepare($sql);
        
    // Here's where the sanitizing/escaping happens:
        
    $stmt->bindParam(':val_1'$_POST['value_1'], PDO::PARAM_INT);
        
    $stmt->bindParam(':val_2'$_POST['value_2'], PDO::PARAM_STR);
        
    $stmt->execute();
    }
    catch(
    PDOException $e) {
        
    error_log(print_r($e1));
        die(
    "<strong class='error'>Sorry, there was a database error. Debug info has been logged.</strong>");

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Oct 2006
    Location
    Ables Springs, Texas
    Posts
    140
    I don't have a problem with INSERT and special characters. Using "mysql_real_escape_string" seems to handle that. It's the UPDATE that I'm concerned with. And, I'm not quite as advanced in understanding the code as you, so please bear with me.

    When I search phpMyAdmin for "Leanin' Tree," then select "Create PHP Code" the result is
    Code:
    SELECT * FROM `programs` WHERE `progname` LIKE \'Leanin\'\' Tree\' LIMIT 0, 30
    Again using phpMyAdmin and UPDATE I entered
    Code:
    update programs set notes='Hello World' where progname='Leanin" Tree'
    and again requested the PHP code, which returned
    Code:
    "\n"
        . "update programs set notes=\'Hello World\' where progname=\'Leanin\" Tree\'";
    Obviously, they are using something that automatically escapes the special character, but what, I do not know. Maybe it's way too complicated for my feeble mind to comprehend and I'm stuck with manual escaping of special characters.

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    Afraid I've never used that feature of phpMyAdmin, and these days I'm only working with PostgreSQL, so I'm not really sure what's going on, or if it's even working correctly. If it is supposed to be outputting code that you can directly copy/paste into a PHP file, I would be inclined to think either it's not doing a very good job of it, or one or both of us misunderstands what it's doing. Wish I could help you more, but if you don't get satisfaction here, you might want to check if there is a phpMyAdmin-specific forum somewhere, where maybe someone can explain if something is wrong, there's a configuration issue, or it's not being used correctly, or whatever.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Sep 2013
    Posts
    221
    The process of encoding data which contains characters MySQL might interpret is called "escaping". You must escape your strings with mysql_real_escape_string, which is a PHP function, not a MySQL function, meaning you have to run it in PHP before you pass your query to the database. You must escape any data that comes into your program from an external source. Any data that isn't escaped is a potential SQL injection.

    You have to escape your data before you build your query. Also, you can build your query programatically using PHP's looping constructs and range:

    // Build tag fields
    $tags = 'tag' . implode(', tag', range(1,30));

    // Escape each value in the uniqkey array
    $values = array_map('mysql_real_escape_string', $uniqkey);

    // implode values with quotes and commas
    $values = "'" . implode("', '", $values) . "'";

    $query = "INSERT INTO alltags (id, $tags) VALUES ('', $values)";

    mysql_query($query) or die(mysql_error());

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
  •  
HTML5 Development Center



Recent Articles