Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Adam Duffield
Adam Duffield
30,494 Points
Posted by Adam Duffield
Adam Duffield
30,494 Points

How to input user data into the database when they use special characters such as ' or " ?

Hi,

I'm currently creating a small time social media website for my portfolio and me and my friends to enjoy. One major bug I have found that I can't manage to resolve is that when we type in a post or comment that contains special characters that may react with my php code it will not input into the database. I have tried htmlspecialchars which doesn't seem to work. Heres some of my code below:

<?php

while($comment = mysql_fetch_assoc($get_comments)){
                $comment_body = $comment['post_body'];
                $comment_body =   htmlspecialchars($comment_body);
                $posted_to = $comment['posted_to'];
                $posted_by = $comment['posted_by'];
                $removed = $comment['post_removed'];
}
?>

I don't want to include anymore irrelevant code, making the variable comment_body display to the screen regardless of it's characters is all I am after.

Many Thanks, Adam

1 Answer

thomascawthorn
thomascawthorn
22,986 Points

Leaving your database open to user inputted commands is often referred to as SQL injection. Wickipedia says

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

It can be a whole lot more serious than misinterpreting the 'or' operator!

My advice would be to not start escaping potential MySQL input yourself and move away from any of the php mysql functions ASAP. (The php mysql functions are full of security issues and have now been depreciated as of php 5.5.)

You should instead use PDO (PHP Data Object), which is covered in some Treehouse videos. Using PDO protects you from SQL Injection out of the box and will therefore fix your problem. Yay! It's not too difficult to transfer over because it sounds like you already have your queries set up.

You should be able to get started with PDO using the first two badges in this course. I think the first badge includes a lot of local server set up you can probably skip. I think later badges talk more about the actual database, but they still might be useful.

In smaller projects, I tend not to work on escaping any other input into the database, and instead concentrate on escaping output. E.g. if a user enters javascript into the form input, when this data is presented again from the database, is the script running? If so, that's where you'll need to escape output with functions like htmlspecialchars.

Having said that, you might want to remove html tags BEFORE database submission using the strip tags function.

There are so many string functions in the manual, so it's best to do a bit of searching around 'escaping output php' to find a good combination.

To keep it simple, switch to PDO and use htmlspecialchars when you represent that data after you pull it from the database.

Hope that helps!

Adam Duffield
Adam Duffield
30,494 Points
Adam Duffield
Adam Duffield
30,494 Points

Thanks for the support! I definetly need to get reading up on PDO statements and make some changes. I managed to fix it after a lot of searching on php.net, I fixed the error by using 

htmlspecialchars($myvariable, ENT_QUOTES)

for inputting the comment into the DB and

htmlspecialchars_decode($myvariable, ENT_QUOTES);

for displaying it from the database.

thomascawthorn
thomascawthorn
22,986 Points

Sounds like a good start - but I wouldn't decode htmlspecialchars when pulling from the database. Try entering php code into your form input:

"<?php echo "<h1>This form input might be insecure</h1>"; ?>"

When it comes to reading this string from the database, you don't want the php to run. Instead you should see exactly the same string as the one you entered. I've got a feeling that decoding htmlspecialchars might make the server process that string as php, and you'll have:

This form input might be insecure

instead of

"<?php echo "<h1>This form input might be insecure</h1>"; ?>"

Also try entering

<script>alert("this form input might be insecure")</script>

Again, if you get an alert box, you probably don't want to decode after retrieving data.

it's likely you're fine, but it's always good to have a little play :-)