Page 1 of 1

inserting user text into db

Posted: Fri Mar 09, 2007 9:27 pm
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

Posted: Fri Mar 09, 2007 9:36 pm
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.

Posted: Fri Mar 09, 2007 9:50 pm
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

Posted: Fri Mar 09, 2007 9:57 pm
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.

Posted: Sat Mar 10, 2007 8:51 am
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... :?

Posted: Sat Mar 10, 2007 1:02 pm
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.

Posted: Sat Mar 10, 2007 1:41 pm
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)