String Containing double quotes is inserted incomplete in DB

Question

Updated The textarea i have provided in the form takes the user input as strings

String Containing double quotes is inserted incomplete in DB..

I have a string inserted in text area as

"Don't worry too much about layout/design/text size, we will often "spice up" (i.e. bold, italic, spacing) your banner for a better overall look.

And when i inserted the string into DB the string get end at

Don't worry too much about layout/design/text size, we will often

and is inserted partially.. What should i do to allow all the single and double quotes to be inserted?

EDIT ON REQUEST

Below Is the query I am using to insert in the database using php

"insert into products_description (products_id, products_name, products_logo_name1, products_logo_name2, products_logo_name3, products_description) values ('" . (int)$products_id . "', 'banner_" . $products_id . "','".$_POST['logoimage1']."', '".$_POST['logoimage2']."', '".$_POST['logoimage3']."', '".mysql_real_escape_string($_POST['description'])."')"

Here mysql_real_escape_string($_POST['description']) is not escaping double quotes and hence truncates in insertion what should be done?

Answer 1

Escape the doublequotes inside the string, like so:

$theString = "Hello, i wonder what all these \"quotes\" are doing in here...";

The backslash will tell the compiler to ignore the "meaning" of the folowing doublequote, and treat it like a normal character (This is what we call "Escaping").

Also check out mysql_real_escape_string() when working with user input (This will automatically escape all dangerous elements in strings for use in a mySQL Database).

Answer 2

LOL
haven't read whole question but I am sure I know the answer

it's being inserted into database all right, then retrieved all right, and then goes into HTML form's field value... ;-)

Well, seriously.
You have to follow your data step by step.
There is some evil code in your application, that makes some evil things.
You have to follow your data flow and check at what stage it gets spoiled
Just print your data out at these steps:

• after receiving form data
• before inserting into database
• after retrieving from database
• before printing back into form

That's your general fault: you take multi-stage process as a single step.
You watch your string being inserted into textarea and next time you see it in this textarea truncated. And you think it's database issue. While you cannot be so sure - there are many steps where database isn't involved. Watch your app as not a solid block but as multiple stage process.

Answer 3

Use the function mysql_real_escape_string() if it's coming from user input.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

Answer 4

As per @COL Sharpnel's Scouldings :-)

Thanks Agauin for making me scould myself.. sometimes its necessary

i echoed the $_POST['description'] and this displayed as

Don't worry too much about layout/design/text size, we will often

and when i used

htmlspecialchars(stripslashes($_POST['description']))

It gave me complete string

"Don't worry too much about layout/design/text size, we will often "spice up" (i.e. bold, italic, spacing) your banner for a better overall look.

> MORAL OF POST: DONT GET OVER CONFIDENT ON SPAGHETTI CODE

Answer 5

You need to escape your quotes.

If your DB is MySQL, pass all your data through the mysql_real_escape_string() function before saving them to the DB.

If you don't do this, you risk major security holes in your code, not just data going missing!

(in case you aren't doing it already, you should also be escaping other data for other purposes as well; eg data being sent back to the browser should be escaped to prevent rogue users adding raw HTML or Javascript code to it to manipulate your site.

There are a number of functions in PHP to deal with adding and removing escape characters and data filtering. If you want your site to be secure, you need to learn these functions and techniques.

[edit]

After seeing your edit:

Firstly, you need to escape all the strings in your query, not just the description, so add escaping to $_POST['logoimage1'], etc, as you'll have the same problems if any of those contain quotes.

However the escaping on the description field looks correct so I don't know why it would be truncated. The man page for mysql_real_escape_string() states that it escapes double and single quotes, so it should be okay for you. You can test this by print()ing the fully escaped SQL string; this will show if there's anything left unescaped.

Shot in the dark - have you checked the maximum length of your description field in the database? That could also cause string truncation.. unlikely though; I imagine if you're inputting with a textarea you'll have set it up to be long enough.