populating fields with user input

[lauralee]

I have set up an administrator's page for editing content in the database. The content that is entered by the user contains several slashes, double quotations marks, and single quotation marks because the content is science based using feet, inches, fractions, etc. I have used htmlspecialchars($text, ENT_QUOTES, 'UTF-8') in both the $POST and populated form that returns the user input, to keep the notations from being confused by the program as php markup, but then the result in the fields that have been entered with a " show the " instead of the " and the content that has a ' for ft returns a syntax error. If I enter the information directly into the fields of the database using cpanel, it shows up in the form correctly. But as soon as the user attempts to edit the field with new information, it is returned in the field with code $quot; for the inches (") and ' for the ft (')3/8" entered becomes 3/8" and the line: Silicone Cornerseal is 3/8" diameter and seals gaps from 3/16"-5/16". Installs in corner of jambs in 1/8" x 3/8" kerf created by the Corner Grooving Tool. Comes in a 100' roll.becomes:Silicone Cornerseal is 3/8" diameter and seals gaps from 3/16"-5/16". Installs in corner of jambs in 1/8" x 3/8" kerf created by the Corner Grooving Tool. Comes in a 100' roll.If I don't use the htmlspecial chars in the $row['description'] to populate the form, the content is cut off after the " for 3/8".Suggestions?

[chokk]

you're using htmlspecialchars() which is causing the problem.. Stop using that and either use prepared statements or preg_replace()

[lauralee]

If I don't use htmlspecialchars in the form that is populated from the database field entries, nothing shows up after the inch ("). So the entry:Silicone Cornerseal is 1/4" diameter and seals gaps from 1/16"-3/16". Installs in corner of jambs in 1/8" x 3/8" kerf created by the Corner Grooving Tool. Comes in a 100' roll.ends up like this:Silicone Cornerseal is 1/4

[lauralee]

If I don't use htmlspecialchars in the $POST for the input field I get the following error:Error updating family: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'roll.', subfamily = '1/4', price = '40.00' ' at line 4

[birbal]

i guess you are not escaping your inputs (eg. special char) before inserting a query. i mean i think you are not using mysql_real_escape_string.if you use htmlspecialchars() at the time of insertion obviousely it will change the special char to html entities in your db. when you will output it it will be shown up as special char litarals.

[lauralee]

Do I need to use mysql_real_escape_string instead of htmspecialchars during the input by the user, but continue using htmspecialchars during the to the form that is populated by the db fields? Below is an example of my UPDATE for the fields that are populated by the db fields. if (isset($_POST['id'])) $id = $_POST['id']; $name = $_POST['name']; $title = $_POST['title']; $description = $_POST['description']; $subfamily = $_POST['subfamily']; $price = $_POST['price'];$name= htmlspecialchars($name);$title= htmlspecialchars($title);$description= htmlspecialchars($description);$subfamily= htmlspecialchars($subfamily, ENT_QUOTES, 'UTF-8');$price= htmlspecialchars($price); $sql = "UPDATE family SET name = '$name', title = '$title', description = '$description', subfamily = '$subfamily', price = '$price' WHERE id ='$id'"; if (!@mysql_query($sql)) { echo '<p>Error updating family: ' . mysql_error() . '</p>'; exit(); }

[birbal]

mysql_real_escape string will prevent from injection attack. it will escape some special chars. you must use it before inserting a queryhtmlspcialchar will convert html special char with htmlentities. it dont escape " ' ", " " ". both are different.here when it founds " or ' in your query it suppose that it is the end of string and it throw a sql error. where as using escaping this will tell mysql that it is not end of your string (input) it is a part of input.

[ShadowMage]

As birbal is getting at, I would try using mysql_real_escape_string when you are saving the input to the database, and when you are retrieving the information and displaying it, then you'd use htmlspecialchars.

[lauralee]

I have included the mysql_real_escape_string in the input UPDATE and htmspecialchars in the display of the form. Which works half way. Now it displays everything except the " for the inches.Below is the code that I use to display the field content in the form.echo '<td><input type="text" name = "description" value ="' . htmlspecialchars($row['description'], ENT_QUOTES, 'UTF-8') . '" size="80" /></td>';Below is what I use in the UPDATE portion$description= mysql_real_escape_string($description);

[lauralee]

Oops! I didn't have the mysql_real_escape_string on the subfamily field, so the " didn't show up for that one field. Thanks for the helpful suggestions. I had completely overlooked the use of mysql_real_escape_string.It appears that everything is working properly now. Thanks, again!

[lauralee]

sorry to bother you again, but I just tried posting a few entries and found that I still have problem when trying to enter something like 100', or 3" x 164'. Anytime I use the apostrophe for the ft abbreviation, the content in the database field no longer displays in the populated form that is used for editing. I have the following in the form:echo '<td><input type="text" name="size" value = "' . htmlspecialchars($row['size'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>';and this code for UPDATE$size = mysql_real_escape_string($size);The entry shows up in the database when I check it through cpanel phpMyAdmin, but it doesn't show up in the editing form.Why doesn't the mysql_real_escape_string work for the ft. (') abbreviation?

[boen_robot]

Do you have it for every string that is in the update? If you miss one, that one can be the cause.There's also one more thing... "abbreviation"?' - that's an apostrophe. Or is that supposed to be another character that happens to look the same?If so (or even otherwise, just in case), set your connection's charset to UTF-8, using mysql_set_charset(), like:

mysql_set_charset('utf8');

Your old data may be unrepairable, but the new should appear properly.

[lauralee]

Yes, it is an apostrophe. I have the code below in an include that is called when needed. Where in the connection code do I put the mysql_set_charset('utf8');?<?php // Connect to the database server $dbcnx = @mysql_connect('localhost', 'myserver', 'password)'); if (!$dbcnx) { exit('<p>Unable to connect to the ' . 'database server at this time.</p>'); }

[chokk]

Any time you open a connection to the database you have to set the charset.Try adding a filterfunction like this:

function filter($var) { 	$var = preg_replace("#'#", "'", $var);	$var = preg_replace("#`#", "‘", $var);	$var = preg_replace('#"#', """, $var);	return $var;}

and send all userinput through it before running your query.

[chokk]

Any time you open a connection to the database you have to set the charset.Try adding a filterfunction like this:

function filter($var) { 	$var = preg_replace("#'#", "'", $var);	$var = preg_replace("#`#", "‘", $var);	$var = preg_replace('#"#', """, $var);	return $var;}

and send all userinput through it before running your query.

Weird.. the single quote html code is turned into a real single quote in this forum... replace "'" with "& #39;" (but remove space between the & and #..

[Ingolme]

Any time you open a connection to the database you have to set the charset.Try adding a filterfunction like this:

function filter($var) { 	$var = preg_replace("#'#", "'", $var);	$var = preg_replace("#`#", "‘", $var);	$var = preg_replace('#"#', """, $var);	return $var;}

and send all userinput through it before running your query.

I wouldn't use preg_replace() for a single character. Use str_replace instead, it's more efficient.Also, you're able to pass arrays as arguments for the function so that you don't have to run it several times.And again, the best filter for putting data into the database is mysql_real_escape_string().

[Fmdpa]

Regarding htmlspecialchars(), it converts certain characters to their respective HTML Entities, thus rendering them useless. This helps prevent XSS, which is triggered by injection of <script> tags. This forum uses some form of it: <b>this would normally be bold text, but since the less-than and greater-than symbols are converted to html entities, the tags are useless</b> You can see the HTML entities if you click the "View Source" option on your browser, and locate this comment. XSS is the most common form of security attacks, so it is very important to prevent it. SQL injection is not as common, but very serious when it does occur. As Birbal said, mysql_real_escape_string() is one way to prevent it. Using this method, you must make sure to always escape the input before inserting it into the db. XSS is slightly different in that it is not effective until the data is outputted. Because of this, you can use an html entities conversion function on the data output.

[lauralee]

Weird.. the single quote html code is turned into a real single quote in this forum... replace "'" with "& #39;" (but remove space between the & and #..

Where do I place the filter code? Just a note....its the singe quote that has given me so much trouble. Even with the ENT_QUOTES in my code, the apostrophe is read as the beginning of html in a php line and everything that follows it is blank, or else the whole line does not show.

[chokk]

Where do I place the filter code? Just a note....its the singe quote that has given me so much trouble. Even with the ENT_QUOTES in my code, the apostrophe is read as the beginning of html in a php line and everything that follows it is blank, or else the whole line does not show.

Well you pass all user submitted data through the function, before you insert into or update your database table.So you have some input..$some_user_input = "whatever the user inputs..";and you stick it into the function...$some_user_input = filter($some_user_input);the data is now ready to be inserted/updated into the database..

[birbal]

if you want to store html special chars as html entitiy in your datbase so there is already htmlspecialchars() which will convert all to html entities. i am not sure why there needs another function to filter it out.i mean$input=htmlsepcialchars($_POST['input']);and then insert $input into the datbase. it will store the ' " ' to its htnl entities and so on...and when you will fetch it in another page it will be shown as its entity valueor as shadowmage said you can simply input it in datbase and use htmlspeciachars() after fetching it from database.echo htmlspecialchars($row['column']);but you need to use mysql_real_escape_string in both way.

[lauralee]

After checking fields on other forms, I found that they work ok, even when using the apostrophe when entering date like 3" x 164'. However, in the products form, it will not enter any of the data of the "size" field when a single quote is used like in 3" x 164'.Although the code looks identical, there must be something different with the "size" field that makes the apostrophe in the input cause the field to be empty after the UPDATE is performed. Except, I just found out that if I enter 3" x 164' in the description field that precedes the size field, and I enter 3" x 164' in the size field at the same time and hit the edit button to perform the UPDATE, both entries show up. Is that weird of what????Can you see anything? I must be overlooking something. Here is the code. Is it somehow not reading the single quote one time, but reading it the second time its used?

  while ($row = mysql_fetch_array($result))      {      echo '<form action=" " method="post">';     echo '<tr><td><input type="text" name="id" value = "' . $row['id'] . '" size="9" /></td>';    echo '<td><input type="text" name="family" value = "' . $row['family'] . '" size="9" /></td>';    echo '<td><input type="text" name="name" value = "' . htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . '" size="15" /></td>';   echo '<td><input type="text" name="description" value = "' . htmlspecialchars($row['description'], ENT_QUOTES, 'UTF-8') . '" size="45" /></td>';    echo '<td><input type="text" name="color" value = "' . htmlspecialchars($row['color'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>';     echo '<td><input type="text" name="size" value = "' . htmlspecialchars($row['size'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>';   echo '<td><input type="text" name="weight" value = "' . htmlspecialchars($row['weight'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>';   echo '<td><input type="text" name="list_price" value = "' . htmlspecialchars($row['list_price'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>';   echo '<td><input type="text" name="cost" value = "' . htmlspecialchars($row['cost'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>';    echo '<td><input type="submit" name="action" value="Edit" /></td>';    if (isset($_POST['id'])) $id = $_POST['id'];$family = $_POST['family'];  $name = $_POST['name'];  $description = $_POST['description'];    $size =  $_POST['size'];  $color =  $_POST['color'];$weight =  $_POST['weight'];$list_price = $_POST['list_price'];$cost = $_POST['cost'];$description = mysql_real_escape_string($description);$family = mysql_real_escape_string($family);$name = mysql_real_escape_string($name);$size = mysql_real_escape_string($size);$color = mysql_real_escape_string($color);$weight = mysql_real_escape_string($weight);$list_price = mysql_real_escape_string($list_price);$cost = mysql_real_escape_string($cost);      $sql = "UPDATE products SET        id = '$id',   family = '$family',        name = '$name',        description = '$description',        size = '$size',        color = '$color',        weight = '$weight',             list_price = '$list_price',        cost = '$cost'                WHERE id ='$id'";        if (!@mysql_query($sql)) {                  echo '<p>Error updating product: ' .               mysql_error() . '</p>';               exit();               }                          

[birbal]

you may want to use [ code] instead of [ codebox] here. [ code] is better for reading. codebox is little wired(i mean to w3schools forum bbcode)mysql_real_escape_string($qry,$con) needs a active connection as its parameter. otherwise it will update a empty (blank) field in your daatbase though your query will show ok..another thing if there is value in $row['size']=3" x 164'

<input type="text" name="size" value = "' . htmlspecialchars($row['size'], ENT_QUOTES, 'UTF-8') . '" size="5" />

it will show up it in input feild as its html entitieslthen when you input it in database it will be stored as htmlentities. (i guess you are not doing so you are directly taking the charcter as literal in your datbase). so you need to decode it before insertion to work it properly i think. otherwise it will be stored as html entities and next time when will do edit again it will be something like encoding to a previously encoded string.so next time when you will do again a update $row['size'] will evauluate a encoded html entities and when you will apply again htmlsepcialchars it will do rencode . so it wil not give desired result i think. you have to do same way in INSERT or UPDATE in both case i think.something like this

$size = mysql_real_escape_string(htmlspecialchars_decode($size),$con);

$con is a active conection here

[lauralee]

If I place a single quote in the description field, both the description field and the size field are updated correctly, however, when I put a single quote in the color field the size field displays the following after the UPDATE:', color = 'But if I enter 3" x 164' roll in the color field, both the size and the color field UPDATE properly. This makes no sense at all!

[boen_robot]

The stuff you do for description... all form fields that are to enter the DB (e.g. "color") need to follow the same pattern. Your problems sound as if you're taking one solution and apply it to only one field (e.g. only on "description").You are missing a lot of brackets ("{" and "}") all over your code... you may want to make sure they're all right.

[lauralee]

No. All of the fields have the same format. They are all entered identically. This is just a strange anomaly. As long as I have one apostrophe in the description field or the color field, the size field will enter correctly. Or, I can even have an apostrophe in both the description field and the color field, and the size field will enter ok. But, if I don't have at least one apostrophe in one of the previous fields, the size field comes up empty no matter what else I enter in any of the fields. All of the entries are the same. I can't see any difference in them. And I can enter 3" x 164' in any other field and it displays properly. Where is the problem?THIS POPULATES THE FORM FOR EDITINGecho '<td><input type="text" name="name" value = "' . htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . '" size="15" /></td>'; echo '<td><input type="text" name="description" value = "' . htmlspecialchars($row['description'], ENT_QUOTES, 'UTF-8') . '" size="45" /></td>'; echo '<td><input type="text" name="color" value = "' . htmlspecialchars($row['color'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>'; echo '<td><input type="text" name="size" value = "' . htmlspecialchars($row['size'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>'; echo '<td><input type="text" name="weight" value = "' . htmlspecialchars($row['weight'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>'; echo '<td><input type="text" name="list_price" value = "' . htmlspecialchars($row['list_price'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>'; echo '<td><input type="text" name="cost" value = "' . htmlspecialchars($row['cost'], ENT_QUOTES, 'UTF-8') . '" size="5" /></td>'; echo '<td><input type="submit" name="action" value="Edit" /></td>';BELOW IS WHAT IS USED TO UPDATE THE DATABASE FIELDSif (isset($_POST['id'])) $id = $_POST['id'];$family = $_POST['family']; $name = $_POST['name']; $description = $_POST['description']; $size = $_POST['size']; $color = $_POST['color'];$weight = $_POST['weight'];$list_price = $_POST['list_price'];$cost = $_POST['cost'];$description = mysql_real_escape_string($description);$family = mysql_real_escape_string($family);$name = mysql_real_escape_string($name);$size = mysql_real_escape_string(htmlspecialchars_decode($size));$color = mysql_real_escape_string($color);$weight = mysql_real_escape_string($weight);$list_price = mysql_real_escape_string($list_price);$cost = mysql_real_escape_string($cost); $sql = "UPDATE products SET id = '$id', family = '$family', name = '$name', description = '$description', size = '$size', color = '$color', weight = '$weight', list_price = '$list_price', cost = '$cost'