Best practice (insertion into mysql)

Hi guys firstly sorry if this is a follow up from another one i previously posted.
Guys im now using mysql_real_escape_string for all my data insertions into mysql.

I just need some help clearing the following example up.

======================
Example of an insert:
======================
The below is the exact thing I am typing in to a textarea field, and I want this to be DISPLAYED back exactly as I type it in.

<?php
echo "hi";
echo "hi";
?>


============================
How im inserting the above:
============================
When I insert it, im inserting it as follows:
$bothCode = $_POST['bothcodearea'];
$bothCode = mysql_real_escape_string($bothCode);

============================
What it looks like in the database:
============================
In the database, it looks like below:

<?php\r\necho \\\"hi\\\";\r\necho \\\"hi\\\";\r\n?>

==============================================
What it looks like on VIEWING the record from a browser:
==============================================
On VIEWING the record, it looks like below:

<?phprnecho \"hi\";rnecho \"hi\";rn?>


================================
Question:
================================
Guys, on viewing in a browser, how do i get it to look like:

<?php
echo "hi";
echo "hi";
?>


That is, exactly how i have typed it in?

On insert, should I be using nl2br?
I did try this, but when I used nl2br in conjunction with mysql_real_escape_string, it didnt work properly.
Should I only use mysql_real_escape_string on insertion, and not use any other function on insert?

Any help greatly appreciated.


LVL 1
Simon336697Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dwaynecharringtonCommented:
The issue is when returning the data back from the database that has been escaped with mysql_real_escape_string, you must unescape the content, otherwise it will be returned with slashes.

Use the function stripslashes($variable) to strip slashes from the content you're pulling out of the database, then echo it.


0
Simon336697Author Commented:
Thanks dwayne will do mate thank you.
0
Simon336697Author Commented:
So dwayne, sorry mate, there is no need to use mysql_real_escape_string on returning data back from the database? that is....when viewing records?
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

dwaynecharringtonCommented:
Whenever you're performing a MySQL query and using variables that contain user inputted data, use mysql_real_escape_string.

Let me elaborate for you:

$username = $_POST['username'];

$sql = "SELECT * FROM users WHERE username='$username'";

As you can see, the user could enter any rogue input with that line. If your query doesn't involve any user input (even for returning SQL items), there is no need for it.

Here is an example of using stripslashes:
(This takes into account there is a value in the users table called age)

// Escape the input
$username = mysql_real_escape_string($_POST['username']);
 
// Query line
 $sql     = "SELECT * FROM users WHERE username='$username'";

// Perform the query
$query = mysql_query($sql);

// Fetch data from the database and put it into an array
$array  = mysql_fetch_array($query);

// Strip the slashes out
$age    = stripslashes($array['email']);

// Echo the stripped data
echo $age;
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Simon336697Author Commented:
Gotcha.....that is great mate....well explained...a very big A++++++++ thank you mate. :>)
0
absxCommented:
Hi Simon336697,

I assume you're only ever going to use that user input for displaying it on a web page? In that case, htmlentities() is your best friend. You can use htmlentities($string) to transform a string into HTML entities (as in "<" becomes "<"). This should be just as MySQL-safe as mysql_real_escape() (do not read "safe and right for every purpose", though) and you can be sure that every character makes it, just as they were.

Example below doesn't concern databases but gives you an image:
<?
$string = "
<?php
echo \"hi\";
echo \"hi\";
?>
";
echo "<pre>".htmlentities($string)."</pre>";
?>

Open in new window

0
Simon336697Author Commented:
Hi absx,
Mate thank you so much for your kind answer....much appreciated and Ill take that onboard. :>)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.