Strategy for securing MySQL PHP application - please comment

[Harold Crump]

Greetings,

I have a fairly vanilla PHP web application that stores and retrieves
data in a MySQL database.
Users will be adding a lot of special characters such as single and
double quotes, accented French characters, etc.

I want to eliminate any potential for XSS or SQL injection attacks.

My question - is it enough to pass all user input through the
htmlentities() function call and store the resultant output?

When displaying database fields back on the page, the browser should
automatically take care of converting the HTML characters.

Am I missing something?
What else do I need to do for making my app immune to XSS and injection
attacks?

Also, I would like to replace all semi-colons in input with something
else - but I am not sure what and how.

All ideas and suggestions welcome - as you can tell I am new to this.

Thanks,
Harold.

[Rik]

Harold Crump wrote:

Greetings,

I have a fairly vanilla PHP web application that stores and retrieves
data in a MySQL database.
Users will be adding a lot of special characters such as single and
double quotes, accented French characters, etc.

I want to eliminate any potential for XSS or SQL injection attacks.

My question - is it enough to pass all user input through the
htmlentities() function call and store the resultant output?

No. Use mysql_real_escape_string(), allthough that's not a 100% secure
either:
http://ilia.ws/archives/103-mysql_re...tatements.html

Prepared statements seem the way to go. Use mysqli if available.

Also, I would like to replace all semi-colons in input with something
else - but I am not sure what and how.

Why?

Grtz,
--
Rik Wasmus

[Harold Crump]

Hi Rik --

My question - is it enough to pass all user input through the
htmlentities() function call and store the resultant output?

No. Use mysql_real_escape_string(), allthough that's not a 100% secure
either:
http://ilia.ws/archives/103-mysql_re...tatements.html

My understanding is that htmlentities replaces all types of quotes with
its HTML equivalent - doesn't that get rid of the whole problem with
escaping, or not escaping, quotes?

What's the issue with storing the &quote in the database?

Why bother with mysql_real_escape_string and all its inherent issues if
we can completely eliminate quotes from making their way into the SQL
statement?

What am I missing?

Prepared statements seem the way to go. Use mysqli if available.

Aren't they available only with version 5 and above?
I am still on an older version.

Also, I would like to replace all semi-colons in input with something
else - but I am not sure what and how.

Why?

Semi-colons are statement terminators in SQL.
They are commonly used in SQL injection attacks to end the current
statement and insert a malicious statement.

-Harold.

[Jerry Stuckle]

Harold Crump wrote:

Hi Rik --

>>>My question - is it enough to pass all user input through the
htmlentities() function call and store the resultant output?

No. Use mysql_real_escape_string(), allthough that's not a 100% secure
either:
http://ilia.ws/archives/103-mysql_re...tatements.html

My understanding is that htmlentities replaces all types of quotes with
its HTML equivalent - doesn't that get rid of the whole problem with
escaping, or not escaping, quotes?

What's the issue with storing the &quote in the database?

What if you want to use the data for other than displaying on the web? For instance, another
(non-web) application is going to print information from the database? It might even be a C/C++
application, for instance.

>
Why bother with mysql_real_escape_string and all its inherent issues if
we can completely eliminate quotes from making their way into the SQL
statement?

Because mysql_real_escape takes the current charset into account when performing its operations.

What am I missing?

The fact that not everything in the world is html based?

>

>>Prepared statements seem the way to go. Use mysqli if available.

Aren't they available only with version 5 and above?
I am still on an older version.

True, but you can secure your code without prepared statements. Additionally, prepared statements
have additional overhead.

>

>>>Also, I would like to replace all semi-colons in input with something
else - but I am not sure what and how.

Why?

Semi-colons are statement terminators in SQL.
They are commonly used in SQL injection attacks to end the current
statement and insert a malicious statement.

So? A semicolon is perfectly valid within a string. Properly escaping your input data will take
care of the problem as any attempt to insert a semicolon and an additional statement will just give
an error due to invalid syntax.

-Harold.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Harold Crump]

What's the issue with storing the &quote in the database?

>
What if you want to use the data for other than displaying on the web? For instance, another
(non-web) application is going to print information from the database? It might even be a C/C++
application, for instance.

Point taken.
This application, however, is web-only.
I don't anticipate any non-web consumer for this data.
If that does indeed come to pass, I figure it will be easy enough to
write a script that HTML decodes everything and saves it back into the
database with escaped characters - no?

Why bother with mysql_real_escape_string and all its inherent issues if
we can completely eliminate quotes from making their way into the SQL
statement?

Because mysql_real_escape takes the current charset into account when performing its operations.

So does htmlentities()

What am I missing?

The fact that not everything in the world is html based?

No?
You mean you don't dream in HTML?
Where're you from? :p

-Harold.

[Rik]

Harold Crump wrote:

>>My question - is it enough to pass all user input through the
htmlentities() function call and store the resultant output?

No. Use mysql_real_escape_string(), allthough that's not a 100%
secure
either:

http://ilia.ws/archives/103-mysql_re...tatements.html

>
My understanding is that htmlentities replaces all types of quotes
with
its HTML equivalent - doesn't that get rid of the whole problem with
escaping, or not escaping, quotes?

Nope. The problem is, it's possible to post 'abnormal' characters to your
script. Don't assume they will always use you simple HTML-form to post data,
people with trouble in mind can post stuff htmlentitities() won't replace.

What's the issue with storing the &quote in the database?

Nothing, you surely ca nmysql_real_escape_string(htmlentities($string)) if
you want that.

Why bother with mysql_real_escape_string and all its inherent issues
if
we can completely eliminate quotes from making their way into the SQL
statement?

htmlentities() won't guarantee that.

>Prepared statements seem the way to go. Use mysqli if available.

Aren't they available only with version 5 and above?
I am still on an older version.

Unfortunately yes.

>>Also, I would like to replace all semi-colons in input with
something
else - but I am not sure what and how.

Why?

Semi-colons are statement terminators in SQL.
They are commonly used in SQL injection attacks to end the current
statement and insert a malicious statement.

Like Jerry said: properly escaping the string will make that harmless, and
your users still able to post the normally valid ; without unexpected
results.

Grtz,
--
Rik Wasmus

[Jerry Stuckle]

Harold Crump wrote:

>>>What's the issue with storing the &quote in the database?

What if you want to use the data for other than displaying on the web? For instance, another
(non-web) application is going to print information from the database? It might even be a C/C++
application, for instance.

Point taken.
This application, however, is web-only.
I don't anticipate any non-web consumer for this data.
If that does indeed come to pass, I figure it will be easy enough to
write a script that HTML decodes everything and saves it back into the
database with escaped characters - no?

First of all, you need to separate the data from the application. You
may very well have multiple applications using the same data.

And *right now* this is a web application. But does that mean it always
will be?

You should always separate your data from the presentation of the data.
&quote is part o the presentation, and should be converted after the
data is retrieved from the database, not before it's inserted.

>

>>>Why bother with mysql_real_escape_string and all its inherent issues if
we can completely eliminate quotes from making their way into the SQL
statement?

Because mysql_real_escape takes the current charset into account when performing its operations.

So does htmlentities()

And no, htmlentities() does not take the current characters set into
account. It only converts specific characters in the Western European
character set to HTML entities.

mysql_real_escape, OTOH, looks at the current charset used by the
connection and converts data in the string to input which is compatible
with mysql. A completely different function, for a completely different
purpose.

Two functions, two uses. Don't get them mixed up!

>

>>>What am I missing?

The fact that not everything in the world is html based?

No?
You mean you don't dream in HTML?
Where're you from? :p

-Harold.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Richard Levasseur]

Harold Crump wrote:

Hi Rik --

My question - is it enough to pass all user input through the
htmlentities() function call and store the resultant output?

No. Use mysql_real_escape_string(), allthough that's not a 100% secure
either:
http://ilia.ws/archives/103-mysql_re...tatements.html

My understanding is that htmlentities replaces all types of quotes with
its HTML equivalent - doesn't that get rid of the whole problem with
escaping, or not escaping, quotes?

What's the issue with storing the &quote in the database?

You should try to store the data as cleanly as possible. It saves
hassle when writing the output, which is the bulk of the code. If you
are sanitizing the input the best you can, then you shouldn't have to
decode the output from the database. Keep in mind you can't fully,
100% secure it, you can just make it not worth the effort of attacking.

>
Why bother with mysql_real_escape_string and all its inherent issues if
we can completely eliminate quotes from making their way into the SQL
statement?

What am I missing?

Prepared statements seem the way to go. Use mysqli if available.

Aren't they available only with version 5 and above?
I am still on an older version.

They became available in MySQL 4.1, which is backwards compatible with
previous client libraries, meaning, you don't need to upgrade PHP. The
only thing you have to do is use the old password hashing function for
the passwords, then everything is dandy (I upgraded from 3.23 to 4.1 a
long time ago and that was the only hiccup I encountered). I highly
recommend upgrading to at least 4.1 for native prepared statements and
subqueries.

>

Also, I would like to replace all semi-colons in input with something
else - but I am not sure what and how.

Why?

Semi-colons are statement terminators in SQL.
They are commonly used in SQL injection attacks to end the current
statement and insert a malicious statement.

-Harold.

Don't forget, \g is also a statement terminator.

[Harold Crump]

Hi --

Jerry Stuckle wrote:

First of all, you need to separate the data from the application. You
may very well have multiple applications using the same data.

You should always separate your data from the presentation of the data.
&quote is part o the presentation, and should be converted after the
data is retrieved from the database, not before it's inserted.

Yes, that makes sense - point taken.
So, am I covering all my bases if I use the following strategy -

Use mysql_real_escape_string for all user input that goes into the
database

Use htmlentities() for all output that goes to a browser presentation

Use html_entity_decode() for all output that goes to non-web
presentations, like a desktop application or a file stream.

What else do I need to do?

Thanks for your input.

-Harold.

[Jerry Stuckle]

Harold Crump wrote:

Hi --

Jerry Stuckle wrote:

>>First of all, you need to separate the data from the application. You
may very well have multiple applications using the same data.

>>You should always separate your data from the presentation of the data.
&quote is part o the presentation, and should be converted after the
data is retrieved from the database, not before it's inserted.

Yes, that makes sense - point taken.
So, am I covering all my bases if I use the following strategy -

Use mysql_real_escape_string for all user input that goes into the
database

Use htmlentities() for all output that goes to a browser presentation

Use html_entity_decode() for all output that goes to non-web
presentations, like a desktop application or a file stream.

What else do I need to do?

Thanks for your input.

-Harold.

You shouldn't need to use html_entity_decode() for output to non-web
presentations, as you shouldn't have html entities in the database anyway.

You should also validate numeric input to ensure it is numeric. It will
prevent other types of SQL injection, i.e.

DELETE FROM table WHERE id=$i;

And the user keys in

'4 OR 1=1'

The result would be:

DELETE FROM table WHERE id=4 OR 1=1;

Deleting everything from your table. The problem doesn't happen in
strings because the entire string would be quoted. Not so for numeric data.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================