Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
  1. #1
    Senior Member
    Join Date
    Aug 2011
    Posts
    217
    Member #
    29153
    Liked
    4 times
    I know they can become a problem so I need to learn how to deal with them.

    I'm using php and MySQL

  2.  

  3. #2
    Senior Member Webzarus's Avatar
    Join Date
    May 2011
    Location
    South Carolina Coast
    Posts
    3,322
    Member #
    27709
    Liked
    770 times
    there's a builtin function in PHP to handle "escaping" certain characters ( the apostrophe being one of them ).

    The function to use is dependent on the version of PHP and MySQL that you are using.

    http://php.net/manual/en/function.my...ape-string.php will work with some older versions, but had been deprecated for some newer versions ( links on the page to the newer stuff )

  4. #3
    Senior Member
    Join Date
    Aug 2011
    Posts
    217
    Member #
    29153
    Liked
    4 times
    Quote Originally Posted by Webzarus, post: 251848, member: 27723
    there's a builtin function in PHP to handle "escaping" certain characters ( the apostrophe being one of them ).

    The function to use is dependent on the version of PHP and MySQL that you are using.

    http://php.net/manual/en/function.my...ape-string.php will work with some older versions, but had been deprecated for some newer versions ( links on the page to the newer stuff )
    So I'm assuming you do this after it is read from the database and not before it is saved in the database. Am I correct?

  5. #4
    Senior Member Webzarus's Avatar
    Join Date
    May 2011
    Location
    South Carolina Coast
    Posts
    3,322
    Member #
    27709
    Liked
    770 times
    No, I believe it's the other way around, or at least it is with ASP... but ASP doesn't have that neat little function, I had to build a special filter I've been using for many years to escape special character before inserting then into the DB... then I just display the data that comes out with no issues.

  6. #5
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    Actually, it's neither. You don't save something to the database after you've read it unless it's been modified in some way (or the user did something weird like submitted a form to update a record without making any changes to the info), and you don't do it after you've read the information because the user will then see the "SQL version" of the text rather than what is supposed to be there. This is fairly common among PHP scripts where the output will show something like "It\'s not O\'Neill\'s turn to hit yet". So you do it immediately before writing to the database, and only then.

    Like WZ said, there is no native ASP function for that. I had to do as he did and write my own (fortunately, it can be done with a few lines of code). With ASP.net, it's easier because you have an SQL parameter collection that you can use right off.

    http://msdn.microsoft.com/en-us/library/ff648339.aspx <-- this will explain the basics as far as how to do things. Personally, I prefer option 2 when possible because it means that if I ever need to move to another platform, I won't have to spend time rewriting queries and generating datasets, since I've already done it the first time. But that's just me.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  7. #6
    WDF Staff veraderock's Avatar
    Join Date
    Jun 2011
    Posts
    172
    Member #
    28229
    Liked
    22 times
    Hi Glenn,

    I'm assuming you mean quotes rather than apostrophes (there are ascii format apostrophes that are different from quotes). You wouldn't need to escape an apostrophe for a database query necessarily, however you would definitely escape either a single or double quote depending on whether you are using single or double quotes in your SQL statement to enclose values.

    It's important to understand why you would escape a string.. In most scripting languages (including PHP) you can enclose strings with either a single or double quote. The difference being that double quotes allow you to enter variables into the string which will be parsed. Single quotes will not result in those same variables being parsed. For example:

    Code:
    <?php
    $hello = "hi";
    $string1 = "$hello Glenn";
    $string2 = '$hello Glenn';
    echo $string1;  // results in hi Glenn
    echo $string2;  // results in $hello Glenn
    ?>
    Now, a similar case is true with SQL statements in that you can use either single or double quotes to enclose values. Depending on which type you use, you would need to escape any of that character within a statement. For example:

    Code:
    insert into my_db_table set title = "This here's my title";
    insert into my_db_table set title = 'This here\'s my title';
    Notice how the second statement has added the escaped quote, whereas the first didn't that's because the second statement is using a single quote to enclose that value. The following would also work:

    Code:
    insert into my_db_table set title = "This is my \"title\"";
    insert into my_db_table set title = 'This is my "title"';
    Note that this is for PHP and MySQL. If you were using something like Microsoft SQL Server, then I believe enclosures must be a single quote, and to escape them you would use two single quotes together. So it's important to remember which language and DB type you're working with.

    Now to tie it all together, you would have to watch your escaping quotes when writing out the SQL in a PHP value. For example:

    Code:
    <?php
    
    $string = "This here's my title";
    $sql = "insert into my_db_table set title = '".str_replace("'","\'",$string)."'";
    
    $sql = "insert into my_db_table set title = 'This here\'s my title'";
    $sql = "insert into my_db_table set title = \"This here's my title\"";
    $sql = 'insert into my_db_table set title = \'This here\\\'s my title\'';
    
    ?>
    All of the above statement produce the same thing, each escaped differently based on the enclosures used (and the first to show an example of a string replacement function). Notice the final statement also has to escape the backslash so that it is carried through to MySQL.

    The function mysql_real_escape_string, or mysqli_real_escape_string, or the PDO::quote method will handle the escaping for you so you don't have to worry about what to escape. For example:

    Code:
    $string = "This here's my title";
    $sql = "insert into my_db_table set title = '".mysql_real_escape_string($string)."'";
    $sql = "insert into my_db_table set title = '".mysqli_real_escape_string($string)."'";
    $sql = "insert into my_db_table set title = '".$pdo_object->quote($string)."'";
    That is the preferred way to insert or update information in your database and means the data in your database won't have any extra "special" formatting. When you read the data afterwards, it will not need to be escaped or otherwise cleaned..

    Now, with PHP, especially if you're dealing with an older PHP install, you may have to deal with "Magic Quotes" which has nothing to do with the database and is your server adding quotes to your content. That is being deprecated from PHP (if not already) as it was understood to be a bad idea, but would be a case for you experiencing some extra slashed quotes unexpectedly..
    Last edited by veraderock; Jun 11th, 2013 at 09:55 PM.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 08:40 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com