?
Solved

Best practice (insertion into mysql)

Posted on 2008-11-04
7
Medium Priority
?
325 Views
Last Modified: 2010-05-18
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.


0
Comment
Question by:Simon336697
  • 4
  • 2
7 Comments
 
LVL 6

Expert Comment

by:dwaynecharrington
ID: 22883201
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
 
LVL 1

Author Comment

by:Simon336697
ID: 22883217
Thanks dwayne will do mate thank you.
0
 
LVL 1

Author Comment

by:Simon336697
ID: 22883224
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Accepted Solution

by:
dwaynecharrington earned 2000 total points
ID: 22883251
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
 
LVL 1

Author Comment

by:Simon336697
ID: 22883650
Gotcha.....that is great mate....well explained...a very big A++++++++ thank you mate. :>)
0
 
LVL 9

Expert Comment

by:absx
ID: 22883651
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
 
LVL 1

Author Comment

by:Simon336697
ID: 22883685
Hi absx,
Mate thank you so much for your kind answer....much appreciated and Ill take that onboard. :>)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month16 days, 21 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question