PHP Store double quotes in mysql database using PDO

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).

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

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

Okay. Good luck :)

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

Please see this article:
https://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.
https://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).

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.

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

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 :)

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;

Select allOpen 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.

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

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.

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

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

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.

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

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!