MySQLi & Escape Strings

[mattyd]

Hello.

I am beginning the process of coverting all of my MySQL to MySQLi.

I have been doing much research on this but find it a bit confusing.

I have two questions at this point regarding the matter:

1) What does it exactly mean to "escape" a string and where does the code for this go? I assume it goes on my page with my database login credentials.

I found the following but find it somewhat hard to interpret:

"We'll use the mysqli_real_escape_string() function. Since it needs a database connection, we'll go ahead and wrap it in its own function. In addition, since we only need to escape strings, we might as well quote the value at the same time:"

`

    function db_quote($value) {
        $connection = db_connect();
        return "'" . mysqli_real_escape_string($connection,$value) . "'";
    }

`
"If we are not sure of the type of value we pass to the database, it's always best to treat it as a string, escape and quote it. Let's look at a common example - form submission. We'll use our previous INSERT query with user input:"

`

    // Quote and escape form submitted values
    $name = db_quote($_POST['username']);
    $email = db_quote($_POST['email']);

    // Insert the values into the database
    $result = db_query("INSERT INTO `users` (`name`,`email`) VALUES (" . $name . "," . $email . ")");

`
2) After I have this set up in my code, how do I properly test that it is indeed working (Without completly wiping out my tables, etc?)

I just really need some further explanations on this subject before I begin the process.

Any resources, advice, or pointers in the right direction would be greatly appreciated.

Thank you in advance!

Matthew

[deceptikon]

What does it exactly mean to "escape" a string and where does the code for this go?

Escaping a string means converting characters that would be treated as special in a query as literal characters. A good example is a single quote. This has special meaning in a query, and attackers can use it to alter the functionality of the query to bypass security. Escaping that character makes it non-functional as part of the query such that it only contributes to a string value. Details on that can be found by studying SQL injection attacks.

As for where it goes, anytime you build a query with uncontrolled values, those values should be escaped.

After I have this set up in my code, how do I properly test that it is indeed working (Without completly wiping out my tables, etc?)

My preference is a combination of extracting the final query for manual testing, and extensive use of a test database so that the integrity of your production database isn't affected during development.