need some clarification on inserting and retrieving text

[sd0t1]

I'm trying to insert our HR Policy into MySql, section by section.
I'm having trouble displaying it on the screen with the proper formatting.

Can someone help me clarify the proper way to take a paragraph of text, insert it into the database. Then retrieve it and display it on the screen with all the proper formatting.

Here's what I'm currently doing.

Here is a sample paragraph
"In order to avoid the awkward use of “he/she”, “him/her”, and “his/her” when referring to employees, the use of the masculine pronoun in these rules shall be interpreted to include the feminine."

I'm inserting it with

// trim white spaces
$section=trim($_POST['section']);
$content= trim($_POST['content']);

if (!get_magic_quotes_gpc()){
        // add slashes to escape text
	$section = addslashes($section);
	$content = addslashes($content);	
}
$query = "insert into hrpolicy values ('', '".$section."', '".$content."')";

Then here is the code to retrieve the data.
Here is what the text looks like in the database now
"In order to avoid the awkward use of “he/she”, “him/her”, and “his/her” when referring to employees, the use of the masculine pronoun in these rules shall be interpreted to include the feminine."

// select from database
$query = "select * from hrpolicy where '".$section."' = '".$section."'";

// here is the line that I retrieve the text with
// I've modified this a dozen times and nothing makes it display correctly
echo nl2br(htmlspecialchars_decode(strip_tags(stripslashes(stripslashes($row['content'])))));

please help, I'm definitely doing something wrong.

 

[jpadie]

first off ensure that you are using a consistent character set between the web server (as served to the browsers), the database and the database connection. I use utf8.

if the text is not utf8 then use iconv to convert it to utf8 before saving it to the database.

save text with normal (\n) line breaks only. then use nl2br(htmlspecialchars($string)) to write to the database.

dont rely on stripslashes to undo magic quotes. instead explicitly turn all magic quoting off in php.ini or htaccess or httpd.conf (or virtual host config).

lastly, expressly use mysql_real_escape_string() to escape the data before writing to the database.

 

[sd0t1]

ok, let me see if I follow.

I added this line to my Apache conf file:
AddDefaultCharset utf-8

My webpage meta tag is:
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

I changed my MySql data table from 'latin1_bin' to 'utf8_general_ci'

My php.ini had magic_quotes_gpc, and magic_quotes_sybase off and magic_quotes_runtime on. I turned it off as well.

I think that covers the first part of your post.

Then I use the post variable without formatting to set the value of $content. Then I wrapped it in nl2br(htmlspecialchars($content)).
Then lastly, I wrapped it in mysql_real_escape_string($content).

Then I got this error.

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\AppServ\

http://www\ccc\insertHrPolicy.php

on line 21

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\AppServ\

http://www\ccc\insertHrPolicy.php

on line 21
1 section inserted into the database.

does the error have something to with the fact that I'm not using mysql_connect or mysql_pconnect?
instead I'm using an OO function 'new MySQLi'?

 

[sd0t1]

update!!!

I turned magic_quotes_gpc back on and now it works.

 

[jpadie]

turn off magic_quotes_gpc. don't rely on it. it is a thing of consummate evil.

Then I wrapped it in nl2br(htmlspecialchars($content)).
Then lastly, I wrapped it in mysql_real_escape_string($content).

misunderstanding.

storing in database: store the raw text only. cleanse it first with mysql_real_escape_string(trim($value));

displaying database columns on a page:

echo nl2br(htmlspecialchars($column));[/page]

and remember that the [i]connection[/i] to the database must also be utf8.  typically this will be set automatically by the server handshake with the client library but keep an eye out ....

[code]
$mysqli->set_charset('utf8');

mysql_real_escape_string() needs a mysql_connect to be established. if you are using the 'so-called' improved extension then you have some options:

1. use the mysqli_real_escape_string() alternative (for procedural style)
2. use prepared statements (this is the best option, imo).
3. use the real_escape_string() method of the mysqli class.

an example of prepared statements

$sql = vsprintf("select * from table where field1='%s' and field2='%s'", array( mysql_real_escape_string(trim($value1)), mysql_real_escape_string(trim($value2))));
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)):
 //do something
endwhile;

$sql = "Select * from table where field1=? and field2=?";
$statement = $mysqli->prepare($sql);
$statement->bind_param('ss', $value1, $value2); //the first argument is a single letter (for each value) designating whether it is string(s) integer (i) double (d) or blob (b).
$statement->execute();
$result = $statement->get_result();  //assuming you are using mysqlnd
while ($row =>$result->fetch_object()):
  print_r($row);
endwhile;
$mysqli->close();

now you may think that the above looks cumbersome. and it does. but you can create a function to take the query and the params and return to you a results object quite easily

function select($query, $params=array()){
  global $mysqli;
  $statement = $mysqli->prepare($sql);
  foreach($params as $param):
     if(is_string($param)):
       if(str_len($param) > 255):
         $mysqli->bind_param('b', $param);
       else: 
         $mysqli->bind_param('s', $param);
       endif;
    elseif(is_int($param):
         $mysqli->bind_param('i', $param);
    else:
         $mysqli->bind_param('d', $param);
    endif;
    $statement->execute();
    return $statement->get_result();  //assuming you are using mysqlnd
}

note that preparing statements takes care of both the enquoting and the escaping, thus providing good protection against sql injection attacks.

It can also be helpful in, say, multiple insert statements

$sql = "insert into table set field1=? and field2=?";
$statement = $mysqli->prepare($sql);
$statement->bind_param('ss', $value1, $value2);

$value1 = 'black';
$value2 = 'blue';
$statement->execute();

$value1 = 'red';
$value2 = 'green';
$statement->execute();
//etc

as a parting shot, I prefer to use PDO over the new mysqli extension. It requires looser typing and abstracts the requirement for binding results sets and also alleviates the need to handle a separate results object, wrapping the whole thing instead in a statement object.