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
Solved

PHP Store double quotes in mysql database using PDO

Posted on 2014-01-21
18
2,237 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 31

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 31

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 109

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 31

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 31

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
 
LVL 109

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 109

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 109

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 109

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 109

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
The viewer will learn how to dynamically set the form action using jQuery.

856 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