Constructing a Query Using Escaped Variables

Hi,

I want to write a insert query in my php that will add new products into an inventory table. Do I need to use 'escaped variables' prior to entry and if so why? (so I understand) and what's the danger from not doing so?

Hope that isn't too little information.

J
RidgejpManaging DirectorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ray PaseurConnect With a Mentor Commented:
It's enough information.  Let's start with the basics.  For this example and answer we will assume you're using object-oriented MySQLi with your own query strings.  Different rules apply to PDO and to prepared statements.

What if you want to add an element named "O'Brien" to the database?  The query string would wind up looking like this:
$sql = "INSERT INTO inventory (name) VALUES ('O'Brien')";

Open in new window

That would "break" because the apostrophes have special meaning in the SQL query string and they are unbalanced.  So we need a way to tell MySQL to ignore the special meaning of the apostrophe in O'Brien and just pass the apostrophe through into the database table.  We do this by escaping O'Brien, something like this:
$safe_name = $mysqli->real_escape_string("O'Brien");
$sql = "INSERT INTO inventory (name) VALUES ('$safe_name')";

Open in new window

Now the query string will have a backslash character that tells MySQL to ignore the meaning of the apostrophe, and the query will look like this:
INSERT INTO inventory (name) VALUES ('O\'Brien')

Open in new window

The danger from not escaping the variables in the query string is that the query will fail because of a syntax error.

More details on how to use PHP and MySQLi, including examples, are in this article:
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
Ray PaseurConnect With a Mentor Commented:
Further to the point, a query string is like a little computer program that drives the MySQL database engine.  It has to be syntactically correct.  If your code is going to inject data into part of the program, it follows that your injected data must also be syntactically correct.  Hence the escape requirement.

With PDO or "prepared statements" your PHP script sends the query string and the data separately; your script does not modify the query string directly.  Thus the requirement to escape the data is removed.

However there is a third issue lurking here... Is the data really  what you want?
0
 
Ray PaseurConnect With a Mentor Commented:
To this last question of data integrity we often apply the advice "Filter Input, Escape Output" and "Accept only known good values."  If you expect an integer, test the external input ($_GET and / or $_POST) to see if it is an integer.  If you expect an English-language name, try discarding all the characters except the English-language alphabet.  There are a number of filter and sanitize algorithms built into PHP, and these are exactly for the purpose of helping you accept only known good values.  In my experience, every request variable should have its own filter/sanitize algorithm.
http://php.net/manual/en/ref.filter.php

Worth reading:
http://terrychay.com/article/php-advent-security-filter-input-escape-output.shtml
http://php.net/manual/en/security.php
Anything from OWASP: https://www.owasp.org/index.php/Main_Page
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.