Solved

Constructing a Query Using Escaped Variables

Posted on 2016-10-11
3
35 Views
Last Modified: 2016-10-15
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
0
Comment
Question by:Ridgejp
  • 3
3 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41838950
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 41838962
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 41838974
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

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!

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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.

759 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

19 Experts available now in Live!

Get 1:1 Help Now