#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1

    Problems with quote marks


    I have a form where some of the fields can contain quote marks (example: I'm fine) and I am having trouble storing/extracting these fields to/from the database. I have tried using back slashes and code to remove those backslashes, but I'm getting errors.
    Code:
    This is in my connection file (called on every page)
    //magic quotes logic
    if ( get_magic_quotes_gpc() ) {
        $_POST      = array_map( 'stripslashes_deep', $_POST );
        $_GET       = array_map( 'stripslashes_deep', $_GET );
        $_COOKIE    = array_map( 'stripslashes_deep', $_COOKIE );
        $_REQUEST   = array_map( 'stripslashes_deep', $_REQUEST );
    }
    I'm looking for the proper (and best) way to accomplish this. Ideally, with code at the INSERT/SELECT part of the query or in the connection file, as opposed to having the recode every line of the input/output.

    Thanks.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    How to properly access a MySQL database with PHP.

    You must not insert raw values into an SQL query. This will make your application vulnerable to SQL injection attacks. You shouldn't insert any values at all and instead use prepared statements as described above.

    If PHP automatically adds backslashes, turn this off. But only after you've rewritten your database code to use prepared statements. Otherwise, you may end up with no security at all.

    Comments on this post

    • badger_fruit agrees : PDO is the proper and best way to accomplish this!
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    I have read the links you provided, thanks. I've also researched prepared statements on a few other 'tutor' sites. I am assuming then, once I have changed my inserts/queries to this format, I won't have issues with quotes? Or are there additional steps I must add to the prepared statement codes to deal with them?
    Thank You.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    677
    Rep Power
    7
    Quotes are KINDA completely on you. Some people favor the double quote since you can just write variables into, and others like myself, use single quotes and step back and forth as needed to help keep clean and sorted.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    Rodney, once you move to prepared statements you won't have to think about quotes anymore. You can even remove the block of code you put in your original message if you won't be pushing this code to unknown servers.

    Triple_nothing...completely different topic man.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    Thanks Dan, then I guess I'll be busy for awhile trying to re write my queries and inserts in prepared statements. I've read a couple tutorial sites and I have examples, so hopefully I won't get stuck too too badly. Thanks for your help, it's appreciated.
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by ManiacDan
    Rodney, once you move to prepared statements you won't have to think about quotes anymore.
    ... if you use MySQLi or PDO with PDO::ATTR_EMULATE_PREPARES turned off (as described in the link). Unfortunately, the PHP people decided to add this little caveat.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    Welp, so far, so good. I wrote an INSERT command using a prepared statement, and I don't have a problem with quote marks. The data went into the database just fine, and if I check it through MyPhPAdmin, it shows there with the quote marks intact. I'll post what I'm using, so you can tell me if it's secure and in the proper format. So far, it's worked properly and does what I need it to do. * $conn = my db connection
    Code:
    // no errors - add the new ad
    $sql='INSERT INTO ads (ad_name, ad_doc, ad_pname, ad_paddy, ad_pcity, ad_pstate, ad_pzip, ad_title, ad_body, ad_dob, ad_race, ad_sex, ad_hair, ad_eyes, ad_height, ad_weight, ad_stats, ad_married, ad_kids, ad_religion, ad_education, ad_job, ad_penpals, ad_hcity, ad_hstate, ad_relocate, ad_reason, ad_release) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
     
    /* Prepare statement */
    $stmt = $conn->prepare($sql);
    if($stmt === false) {
      trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
    }
     
    /* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
    $stmt->bind_param('ssssssssssssssssssssssssssss',$ad_name,$ad_doc,$ad_pname,$ad_paddy,$ad_pcity,$ad_pstate,$ad_pzip,$ad_title,$ad_body,$ad_dob,$ad_race,$ad_sex,$ad_hair,$ad_eyes,$ad_height,$ad_weight,$ad_stats,$ad_married,$ad_kids,$ad_religion,$ad_education,$ad_job,$ad_penpals,$ad_hcity,$ad_hstate,$ad_relocate,$ad_reason,$ad_release);
     
    /* Execute statement */
    $stmt->execute();
    Only issue I have is with the database itself. There are two default inserts ad_id (unique key auto increment) and ad_date (date current_timestamp). It won't let me set the default to NOW() and I didn't want the time only yyyy-mm-dd (apparant;y getdate() is no longer functional). I guess this is no big deal, I can convert it for display, I just wonder if there's a way to set just yyyy-mm-dd as the default insertion.

    Secondly, and pardon me for the huge post... I'm struggling with the FETCH part of a prepared statement. I'm trying to get all the data from the table where ad_city = $ad_city (a variable set by a form using $_POST). The variable sets properly, but I'm not sure where the fecth (to display the info) fits in. This is what I have now.
    Code:
    // Select query
    $sql='SELECT * FROM ads WHERE ad_pname = ?;
    /* Prepare statement */
    $stmt = $conn->prepare($sql);
    if($stmt === false) {
      trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
    }
     
    /* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
    $stmt->bind_param('s',$ad_pname);
    
    Iterate over results
    $stmt->bind_result($ad_pname);
    while ($stmt->fetch()) {
      - This is where I am trying to put my display -
    }
    
    Close statement
    $stmt->close();
    Last edited by RodneyB; January 22nd, 2014 at 10:29 PM. Reason: Correction
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Sorry for being so insistent, but I fear you've again chosen a bad information source.

    The techniques you've copypasted are extremely cumbersome, and part of this doesn't even make sense. I'm repeating myself, but it might be a good idea to follow the link I gave you. It's all explained there.

    Maybe my explanations are so terrible that you had to look somewhere else. But at least I've checked how PDO/MySQLi works before I wrote a text about it -- which obviously isn't true for the tutorial you have found.

    • Use PDO unless you have a specific reason for going with MySQLi. PDO is much more convenient and works with different database systems
    • Use named parameters instead of bind_param('sssssssssssssssssssssssssssssssssssssssssssssssssssssssss...', ...)
    • Get rid of this weird error reporting stuff. Both PDO and MySQLi have built-in error reporting.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    Code:
    // fetch rows 
    while ($posts_stmt->fetch()) 
        echo html_escape('Post ' . $post_id . ' from ' . $post_created_on . ' says: ' . $post_content) . '<br/>';
    I did read what you posted (several times), but this POST FROM SAYS makes zero sense to me, Trying to adapt this to give me ALL the table fields (I have 30 of them), from a single inputted variable, was a nightmare. I spent almost 2 hours and never once got anything to display on the page.
    I got the code I am using (that works so far) from Here
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    If you have a question about a link I gave you, then why don't you simply ask? That might be better than spending 2 hours on the problem, going to some other source, ending up with a crap tutorial and finally asking us to fix it.

    As I already pointed out above, you should use PDO. Not MySQLi. The problem of MySQLi is that it's a low-level library which is generally difficult to use for a beginner. I'm not too surprised you're struggling with it. With PDO, most problems should vanish. It has no bind_result() and stuff like that. You simply use a foreach loop to iterate through the result set.

    Here's the PDO section. You should try it out. If you have a question, simply ask.
    Last edited by Jacques1; January 22nd, 2014 at 11:38 PM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    I like to try and do something on my own first, before I ask for help... otherwise, I am not learning anything. I want to understand how/why it works, not just that it does work.
    PHP Code:
    $ad_pname $_POST['ad_pname'];

    // prepared statement: get all ad posts  
    $posts_stmt $database->prepare(
        SELECT 
            * 
        FROM 
            ads 
        WHERE 
            ad_pname = :ad_pname
        GROUP BY
           ad_pname 
        ORDER BY 
            ad_pname ASC 
    '
    ); 

    // execute statement, binding values to the parameters 
    $posts_stmt->execute(array( 
        
    'ad_pname' => $ad_pname 
    )); 

    // fetch ads 
    foreach ($posts_stmt as $ad) { 
        echo 
    html_escape(' -absolutely no idea what goes here to get all the fields to display -) . '<br/>'; 

    I don't understand the use of $ad since I see no place that var is set (I don't have a field in my ads table called ad). Also, from what I understand (and I will quote it from the sites I read it on)
    Code:
    so h() is an alias for html_escape() and they convert the following 4
    characters <   >    &    " into &lt;   &gt;  &amp;  &quot; the single quote is not converted...
    If the above is true, then the fucntion will not work for me, since it is only single quotes I am working with.
    NOTE: The connection file is copy/pasted directly from your tutorial post.
  24. #13
  25. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    Originally Posted by RodneyB
    I don't understand the use of $ad since I see no place that var is set
    If you're talking about the $ad in this line

    PHP Code:
    foreach ($posts_stmt as $ad) { 
    ... then it's CREATING that variable for use within the foreach loop.

    foreach ( $arrayname as $key => $value ) ...
    Read the manual
    Last edited by badger_fruit; January 23rd, 2014 at 06:33 AM. Reason: Added link to PHP Manual for "foreach"
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by RodneyB
    Also, from what I understand (and I will quote it from the sites I read it on)
    Code:
    so h() is an alias for html_escape() and they convert the following 4
    characters <   >    &    " into &lt;   &gt;  &amp;  &quot; the single quote is not converted...
    Um, you somehow went to a forum for the programming language Ruby. This is a very nice language, but it has nothing to do with PHP.

    The html_escape() in my code is simply a dummy function call to point out that you have to escape this part of the code for HTML. You can define the function yourself, or you can do something like

    PHP Code:
    echo htmlspecialchars($your_valueENT_QUOTES'UTF-8'
    The code in the explanations isn't necessarly C&P-ready. It's really meant as an explanation to demonstrate the principle.
    Last edited by Jacques1; January 23rd, 2014 at 06:23 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo