Solved

Constructing a Query Using Escaped Variables

Posted on 2016-10-11
3
68 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 110

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 110

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 110

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

626 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