SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Backslashes in database

    I always use mysqli_real_escape_string when I insert data through a form, but I was wondering if it makes any sense to insert data with escaped characters when I insert data directly through PhpMyAdmin (when I first populate the database). Is it important to use backslashes in this case too?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Without looking at the source I can't be entirely sure, but my guess would be PHPMyAdmin executes mysql_real_escape_string (or similar) itself before it queries.
    Why don't you try and see what happens?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried inserting "it's" through PhpMyAdmin and the apostrophe wasn't backslashed.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    So that's good then! Problem solved
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    So that's good then! Problem solved
    That's not what I meant I was talking from a security point of view.

    Let me explain better: as far as I understand, using mysqli_real_escape_string (for example when a user enters data in a form) helps prevent attacks like SQL injection by escaping characters like apostrophes which can be dangerous. My question is: if I insert data directly through PhpMyAdmin, which means that I have direct access to the database, does it have any sense to escape characters?

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    are you going to try to deliberatley compromise your db? If not, then you 'could' avoid the necessity to escape everything. However, if you are concerned with the possibility of someone else accessing your phpMyAdmin*, then I am not sure how you would secure it given, as you say, that it doesn't escape anything.

    (*or any other db admin tool).

    That said, I am not anything like the expert that others arond here are.

    bazz

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    further thought.....
    If such security is a real concern you could, perhaps, change the login privileges each time you want to login to your db admin tool.

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,786
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by D3V4 View Post
    That's not what I meant I was talking from a security point of view.
    mysqli_real_escape_string has NOTHING WHATEVER to do with security. Its function is to prevent the data and the query getting mixed up so that the server doesn't know which is which.

    You should validate your data when you first read it to ensure that the field content is valid for what the field is allowed to contain - as a side effect that prevents injection by not allowing the weird strings required for injection to be entered into fields where they don't make sense in the first place.

    Only where legitimate content could accidentally result in crashing the query does mysqli_real_escape_string play a part and then only if you jumble the sql and data together instead of using prepare/bind.

    For example: you wouldn't allow apostrophes in a username and so the username anything' OR 'x'='x should fail validation for that field long before it gets anywhere near the SQL.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    mysqli_real_escape_string has NOTHING WHATEVER to do with security. Its function is to prevent the data and the query getting mixed up so that the server doesn't know which is which.

    You should validate your data when you first read it to ensure that the field content is valid for what the field is allowed to contain - as a side effect that prevents injection by not allowing the weird strings required for injection to be entered into fields where they don't make sense in the first place.

    Only where legitimate content could accidentally result in crashing the query does mysqli_real_escape_string play a part and then only if you jumble the sql and data together instead of using prepare/bind.

    For example: you wouldn't allow apostrophes in a username and so the username anything' OR 'x'='x should fail validation for that field long before it gets anywhere near the SQL.
    So, even if you validate your data before inserting it into the database, it's always better to use mysqli_real_escape_string anyways?

    And so I can avoid putting backslashes when I insert entries into the db, if I understand it right?

  10. #10
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    are you going to try to deliberatley compromise your db? If not, then you 'could' avoid the necessity to escape everything. However, if you are concerned with the possibility of someone else accessing your phpMyAdmin*, then I am not sure how you would secure it given, as you say, that it doesn't escape anything.

    (*or any other db admin tool).

    That said, I am not anything like the expert that others arond here are.

    bazz
    No, I'm not trying to damage anything xD I was just thinking about this theoretically

  11. #11
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,786
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by D3V4 View Post
    So, even if you validate your data before inserting it into the database, it's always better to use mysqli_real_escape_string anyways?

    And so I can avoid putting backslashes when I insert entries into the db, if I understand it right?
    If you use prepare/bind then you don't need mysqli_real_escape_string at all since the SQL goes in the prepare statement and the data goes in the bind statement and there is no possibility whatever of them getting mixed up.

    If you jumble the SQL and data together in once call then you must use mysqli_real_escape_string on any of the data which can validly contain a character that might be misinterpreted as part of the SQL rather than as part of the data - for example any field that is allowed to contain an apostrophe such as a surname field. You don't need it on any of the other data fields that are not allowed to contain any of the characters that mysqli_real_escape_string escapes as on those fields calling it will do nothing whatever (and so it doesn't do any harm to have it there either since doing nothing is the same whether it is there or not).
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

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