inserting user text into db

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

inserting user text into db

Post by Dave2000 »

How to people recommend that i ensure text input by a user is safe to both insert into database and display on a page?

I used to use only use htmlentities before inserting into the mysql database... I am not thinking (well.... i know) this isn't enough :( Or is it?

Should i insert into database with mysql_real_escape_string(), and then when i extract from database (before i display on page) use htmlentities()? Would this be enough? :?

Shears
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

htmlspecialchars() will protect you from XSS attacks -- if you intent to display user input, you should run it through htmlspecialchars() either before or after it's pushed to the browser (doing it afterwards will save some space in your database). mysql_real_escape_string() will protect you from SQL injection, so long as your values are single-quoted inside the query. If not, use is_numeric() to validate the value.
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Post by Dave2000 »

Hi aaronhall,

Thank you for your reply :)

Wouldn't htmlentities() be better than htmlspecialchars() - as it seems to do the same as htmlspecialchars() and more...

Anyway, wouldn't doing a htmlentities() or htmlspecialchars() before inserting into database also protect against SQL injection?

Shears
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

htmlspecialchars() only encodes those characters that will be interpreted by the browser as XHTML -- htmlentities() is superfluous if you're only using it to protect against XSS attacks.

I won't go into the whole bit about SQL injection, but the vulnerability is that unescaped quotes will allow the attacker to jump out of the value declaration and write in some SQL of his own (like DELETE * FROM table). htmlentities() won't properly escape quotes for SQL queries. There are plenty of SQL injection articles online that would be helpful to look at.
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Post by Dave2000 »

aaronhall wrote:htmlspecialchars() only encodes those characters that will be interpreted by the browser as XHTML -- htmlentities() is superfluous if you're only using it to protect against XSS attacks.

I won't go into the whole bit about SQL injection, but the vulnerability is that unescaped quotes will allow the attacker to jump out of the value declaration and write in some SQL of his own (like DELETE * FROM table). htmlentities() won't properly escape quotes for SQL queries. There are plenty of SQL injection articles online that would be helpful to look at.
Thank you. I know what SQL injection is. I notice that, in the SMF forum code, only htmlspecialchars() is used before inserting into the database, yet you said that is not sufficient... :?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Shears wrote:
Thank you. I know what SQL injection is. I notice that, in the SMF forum code, only htmlspecialchars() is used before inserting into the database, yet you said that is not sufficient... :?
aaronhall is correct, mysql_real_escape_string() should always be used. Perhaps read the manual on this function to understand everything it escapes.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Shears wrote:I notice that, in the SMF forum code, only htmlspecialchars() is used before inserting into the database
smf uses addslashes() which is similar but not the same (and may be insufficient, too)
Post Reply