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?
 
dwaynecharringtonConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.