Solved

PHP Store double quotes in mysql database using PDO

Posted on 2014-01-21
18
2,143 Views
Last Modified: 2014-01-23
What's the best way to store double quotes in a mysql database?  I'm using PDO and have seen some posts that suggest using mysqli_real_escape_string.  I don't understand how that works with PDO and not sure if that's the best way.

Thanks,

Steve
0
Comment
Question by:stkoontz
  • 8
  • 6
  • 4
18 Comments
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 39798288
You don't need mysqli_real_escape_string with pdo (and anyway you couldn't use it). PDO is smart enought to do all dirty work behind the scenes if you use prepared statements (see here also: http://www.php.net/manual/en/pdo.prepare.php).
0
 
LVL 2

Author Comment

by:stkoontz
ID: 39798305
I'm using prepared statements, but everything after the quotes gets truncated.

Here's a sample statement

$query = $qry_registrants->prepare('INSERT INTO tableName (
occupation)
VALUES (
:occupation)');

$query->bindParam(':occupation', $var_occupation, PDO::PARAM_INT);

$query->execute();

Thanks for the quick reply.

Steve
0
 
LVL 2

Author Comment

by:stkoontz
ID: 39798314
I just checked the variable before inserting into the database and the variable was truncated.  I'll run some more tests to try to narrow down the code that's dropping everything after the quotes.

Steve
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 39798319
Okay. Good luck :)
0
 
LVL 2

Author Comment

by:stkoontz
ID: 39798332
I found it.  I'm using hidden inputs to pass the variables.  When the double quotes hit the input, it truncates.

Here's what I see when I look at the code behind the page.

<input name="occupation" type="text" id="occupation" value="Occupation'"Test" />

Any ideas?

Steve
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39798444
Please see this article:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12241-Quotation-Marks-in-PHP.html

You can escape quotes in some places with a backslash.  MySQL has appropriate functions for this.  PDO does not care.  This is a long article, but it's worth your effort to go through it.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

But HTML does care, and you can find what HTML sent to PHP by using var_dump($_REQUEST).
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 39799113
Yes, you have to escape double quotes

<input name="occupation" type="text" id="occupation" value="Occupation\"Test" />

But this will give you Occupation"Test. In addition you had both a single quote and a double quote and I0m a bit confused. Please, let me know what is the output you need, that is the exact value you want to store in the db, so I'll try to give you a better help.
0
 
LVL 2

Author Comment

by:stkoontz
ID: 39799599
I'm developing a conference registration system.  There are fields - like occupation - where whatever is entered needs to be stored in the database.  Another page collects medical information.  One of the fields is 'medications'  If someone uses quotation marks in the medication field and whatever is typed after the quotation mark is truncated, that could be very bad.

I put the single quote and double quote in just as a test.

Thanks for the help.  I greatly appreciate it.

Steve
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 39799666
Okay, so the problem is: if user type in a input text (or in a text area) soem text like: my occupation is "code trader" something in your code truncate everything after the first quote and you store in db only

                 my occupation is"

Is it right?
If you want/can post here your code, we can try to see if we find the responsible line of the issue :)
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39799747
If someone uses quotation marks in the medication field...
Yeah, it's important to get this right.

Start here: http://www.laprbass.com/RAY_temp_stkoontz.php

This script will show you exactly what is coming through into the PHP script.  It also shows what you must do when you send data to the browser.  You can use htmlspecialchars() on any browser output you create.  You must use it on any external data, including the external data that your PHP script has acquired from the data base.  For more information about this, see AntiPractices 20 and 22.

<?php // RAY_temp_stkoontz.php
error_reporting(E_ALL);


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28344441.html


// SHOW WHAT WAS POSTED, IF ANYTHING
$thing = (!empty($_POST['thing'])) ? $_POST['thing'] : NULL;
var_dump($_POST);

// MAKE THE DATA SAFE FOR BROWSER OUTPUT
$safe_thing = htmlspecialchars($thing);

// CREATE A FORM TO RECEIVE INPUT FROM THE BROWSER
$form = <<<ENDFORM
<form method="post">
Enter Anything:
<input name="thing" value="$safe_thing" />
<input type="submit" />
</form>
ENDFORM;

echo $form;

Open in new window

Now that we know what it takes to acquire and display the data, let's turn to the preparation of the data for use in a MySQL query.  Your choice of data preparation methods will depend on your choice of data base extensions.  PDO uses prepared queries, a system that sends the query string separately from the data.  MySQLi puts the data directly into the query string, and for that reason it requires the use of an escape process to nullify the programmatic meanings associated with quotes and nuls.  The article about data base extensions tells you all about it.
0
 
LVL 2

Author Comment

by:stkoontz
ID: 39799831
Ray:  I read through "antiPractices" and I have a lot to learn.  

You must use it on any external data, including the external data that your PHP script has acquired from the data base.

Since I'm using PDO, for security reasons, do I still need to use htmlspecialchars()?  

I'll look through your sample script that shows what's coming through into the PHP script when I have time after lunch.

Thanks,

Steve
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39799887
Since I'm using PDO, for security reasons, do I still need to use htmlspecialchars()?
Yes!  

PDO's "security" comes from using separate data strings to transmit the query and the data.  As such it makes SQL injection impossible because there is never any external data put into the query string.

But whatever you've put into the data base will come back from the data base unalloyed.  Consider the possibility that a malicious user entered something like this into a forum:

<script type="text/JavaScript">window.location('Evil.Malware.com');</script>

If your script echos that to the browser, the browser will run the JavaScript and the client will be taken to the Evil domain of the Malware.com site.  Not so good.  But PHP has a built-in solution to this risk that is absolutely foolproof if used correctly.  When you transform the data with htmlspecialchars() the data becomes this:

&lt;script type=&quot;text/JavaScript&quot;&gt;window.location('Evil.Malware.com');&lt;/script&gt;

Notice what happened to the wickets and quotes?  They will display correctly in the client browser because the entities like &lt; and &quot; are rendered by the browser in a way that is visible, rather than programmatically affective.  The client will see the JavaScript but the browser will not run it, and the attack will have been thwarted.
0
 
LVL 2

Author Comment

by:stkoontz
ID: 39803159
Ray:  I've looked through your posts, played with the code, and have 2 questions.

1) Do I use htmlspecialchar() when I store the fields in the database or when I display the information in the browser?  

2) What's the best way to allow the input of quotation marks when I'm using hidden input fields to pass the data between pages?  I tried addslashes(), but it still truncated the variable.

<input name="lname" type="hidden" id="lname" value="test\"marks" />

Thanks for helping a guy who has a lot to learn.

Steve
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39803202
1. No, I would store the original data and use htmlspecialchars() to make it safe for output.  If you mung the data before storing it in the database, you may find that it's not useful for queries any more.  Also, it gets larger when it's escaped for output, so there is a risk of truncation, which MySQL will do silently.

2. The general design pattern shown in this comment should be workable for hidden input controls, too.  I have not tested it but I see no reason it would fail.  But that said, I would avoid passing hidden input controls between pages.  You can use the PHP session instead.  It's safer because the data is not exposed in clear text and is less subject to tampering.  Sessions are easy if you don't overthink them.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11909-PHP-Sessions-Simpler-Than-You-May-Think.html
0
 
LVL 2

Author Comment

by:stkoontz
ID: 39803591
I read through your articles and ran one test session variable through the system.  It stored the quotes in the database perfectly.  :)

If I set up each variable at the beginning of the page like this...

$var_lname=htmlspecialchars($_SESSION['var_lname'] = (isset($_POST['lname']) ? $_POST['lname'] : null));

Can I use...

echo $var_lname

throughout the page without needing to 'specialchars' it each time?

I read that the session times out after 24 minutes.  If a registrant takes more than 24 minutes, is there an elegant way to let them know that they'll have to start over?

Thanks again for all your help.

Steve
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39804242
throughout the page without needing to 'specialchars' it each time?
Yes.  Once the variable is encoded for safe output it stays safe.  Unless you overwrite it.
... is there an elegant way to let them know that they'll have to start over?
No and they'll hate you for making them work for 24 minutes then throwing their work away!  Please read the article on session handling.

The session lasts 24 minutes per request where a request equals a page load or an AJAX request.  If you've got a form that takes 24 minutes to fill out you have a problem, but that's for another conversation.

If you really think that a client will need more than 24 minutes on a page you've got a couple of choices.  You can send each form element as it is filled in via an AJAX request.  Or you can put your own cookie on the client browser and store the stateful data in your data base.  As a practical matter the second approach is probably better because it will let you remember the client over a long period of time.  They could fill out half the form today, and finish tomorrow without losing their data.  Shopping carts (from smart companies) do this.
0
 
LVL 2

Author Comment

by:stkoontz
ID: 39804303
24 minutes will be plenty of time for the people who go straight through the system.  It's the people that start the registration process, stop to get a cup of coffee, go out to get their mail, etc. that will have the trouble.

Steve
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39804345
Yes.  They will have trouble.  Most notably they will have trouble because some troublemaker will come along and complete their registration while they are away from the computer!
0

Featured Post

Highfive Gives IT Their Time Back

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

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

757 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

22 Experts available now in Live!

Get 1:1 Help Now