Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Constructing a Query Using Escaped Variables

Posted on 2016-10-11
3
Medium Priority
?
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

722 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