Solved

Constructing a Query Using Escaped Variables

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
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.

763 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