whe  would I need to use real_escape_string()  function in mysqli class

Posted on 2011-10-03
Last Modified: 2012-08-13
whe  would I need to use real_escape_string()  function in mysqli class I read the
explantion in

but im not sure I understand what it does and when to use it
Question by:Nura111
    LVL 30

    Expert Comment

    by:Marco Gasi
    A note: your link is to striplslahes not to real_escape_string() function: a typo?

    real_escape_string() function places a backslash before some dangerous characters such as NUL (ASCII 0), \n, \r, \, ', ", and Control-Z. ( This is useful every time you have to insert values into a database preventing to insert bad data. As php manual says, you have to use this function every time you send a query to mysql.
    LVL 107

    Expert Comment

    by:Ray Paseur
    Use it on everything that is not an internally generated integer.  It will cause no harm and may save your bacon some day!

    Author Comment

    is ti also a security issue if not using it?
    LVL 107

    Expert Comment

    by:Ray Paseur
    Yes, it can prevent a foreign agent from injecting information into your queries.  Look up SQL injection on this and other sites
    LVL 27

    Accepted Solution

    SQL Injection hacking uses certain characters to get access to your database structure and from that to your data.  mysql_real_escape_string() safeties those characters so they cannot be used in a query for hacking purposes.

    ALL inputs, and I mean ALL, even hidden inputs, need to be safetied against hacking if they are ever used in any way in any query.

    Most inputs should be checked directly ... if it is supposed to be a number, check that it is a number, if it is supposed to be one of three values, check that it is one of those three values, and etc.

    Text input is the big problem, since it can be made to contain those special characters, and is not easily checked with a whitelist or value type, so you need to use a safety mechanism such as mysql_real_escape_string() to guard it.

    EVERY input must be checked, remember that and you won't be coming back here next month asking how to recover from hacking :):)

    Author Comment

    Is it the same issue as not using htmlspecialchars() on a input from a user?
    Is there a need to use both?

    Author Comment

    another thing : so you mean to use:  mysql_real_escape_string()
    on the input before its been used to insert to the db or other right?

    so what is the issue when using {$row['id']} in an html form e.g
     without htmlspecialchars() before I been told its a secruity issue as well I don't understand why
    LVL 27

    Expert Comment

    You do not need to use both.  I personally prefer and use htmlspecialchars() on all text input.

    The difference is that mysql_real_escape_string just safeties the SQL injection hacking, but not certain other kinds of things.   htmlspecialchars changes ALL special characters into their html &#xxx code equivalents.

    The only drawback to using htmlspecialchars is that it takes a few more bytes in your database ... each special character takes 5 bytes instead of 1 ... so if you have a LOT of text with special characters stored, that might be a problem.

    The advantage of using htmlspecialchars is that it safeties more things, for example not letting some evil javascript into your database that might be exploited later.
    LVL 107

    Assisted Solution

    by:Ray Paseur
    If you accept and store any external textual input, you need to filter it.  If, for example, you're expecting an English-language name you can expect that it will contain alphabetic characters, the space, apostrophe, hyphen, comma, and the period.  Example:

    Leonard O'Pinth-Garnell, Jr.

    A regular expression that would remove all the other stuff would look something like this (the caret inside the character class implies negation):

    /[^ A-Z-,.]/i

    So each time you received a name from an external source, you would apply the regular expression and you would thereby accept only known good values for your name field.

    If you have unfiltered or marginally filtered data in your data base, you need to escape it before you send it to the browser output stream.  Otherwise you risk becoming an attack vector against your clients.  Example:

    echo '<td>';
    echo htmlentities($row["usertext"]);
    echo '</td>';

    I prefer to escape the data on the outbound side of things, rather than escaping the data before storing it.  If you escape before you put the data into the data base, you may have munged the data, and in any case, you have not stored the data that was sent to you, you have stored something that is a derivative work.  Search algorithms may not work correctly on the escaped data.  The origin of the term is uncertain, but a mung operation makes changes to the data in a way that may be irrevocable and that loses some of the original information.

    Required reading:

    Old, but still valuable:

    Now having said that, you need to be aware that the whole field of security is growing rapidly.  The US Government has just put a crack team of 115 engineers to work at the National Security Agency for the purpose of studying the field.  The University of Maryland offers a full-time, four-year college major in IT Security.  So you must not assume that you can follow a few "best practices" and things will work out OK.  You need to be learning about the new threats all the time and reviewing your code to see if there are new threats that might cause you to need to refactor your old code.  It's never going to end, so you might as well join the rest of us who do our best to keep up to date on the threats that are around us.

    Best regards, ~Ray

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now