Question on SQLinjection and XSS

[Petra Meier]

Hello,

if I use the following function for all my mySql commands in php, am I
protected against all SQLinjections and XSS attacks?

function sanitize($value){ return
mysql_real_escape_string(htmlspecialchars($value)) ; }

and i.e.

$query = "insert into users set username='".sanitize($username)."'";
and
$query = "select * from users where
username='".sanitize($username)."'";

or do I miss something here?

thanks

pm

[Michael Fesser]

..oO(Petra Meier)

>if I use the following function for all my mySql commands in php, am I
protected against all SQLinjections and XSS attacks?

function sanitize($value){ return
mysql_real_escape_string(htmlspecialchars($value) ); }

htmlspecialchars() is not meant for sanitizing data input. You should
only apply it on output.

Additionally SQL injection and XSS are two entirely different things,
which don't have anything in common. To avoid XSS every user-submitted
data which is printed to an HTML page again should be passed through
htmlspecialchars(). This not only applies to data from a database, but
also to form submissions like a search form for example.

>and i.e.

$query = "insert into users set username='".sanitize($username)."'";
and
$query = "select * from users where
username='".sanitize($username)."'";

or do I miss something here?

You could also have a look at PDO for another type of queries, which use
typed parameters (called "prepared statements").

Micha

[Petra Meier]

On Jul 10, 8:03*am, Michael Fesser <[e-mail]:

.oO(Petra Meier)

if I use the following function for all my mySql commands in php, am I
protected against all SQLinjections and XSS attacks?

function sanitize($value){ return
mysql_real_escape_string(htmlspecialchars($value)) ; }

htmlspecialchars() is not meant for sanitizing data input. You should
only apply it on output.

Additionally SQL injection and XSS are two entirely different things,
which don't have anything in common. To avoid XSS every user-submitted
data which is printed to an HTML page again should be passed through
htmlspecialchars(). This not only applies to data from a database, but
also to form submissions like a search form for example.

and i.e.

$query = "insert into users set username='".sanitize($username)."'";
and
$query = "select * from users where
username='".sanitize($username)."'";

or do I miss something here?

You could also have a look at PDO for another type of queries, which use
typed parameters (called "prepared statements").

Micha

thanks for your quick reply!
I know that SQLinjections and XSS are different beasts. for that
reason I would use
mysql_real_escape_string against SQLinjections (entering javascript
<scripttags would still work here)
and
htmlspecialchars against XSS (does not cope all possible SQLinjection
attempts)

and for convenience I'd like to use them nested.
Thanks for the hit to look at PDO, I will for future projects, yet
with this one I have to deal with legacy code and don't want to recode
much.

So still the question if I let all values run through both methods
when doing CRUD on mysql, is there still a security hole?

thank you
pm

[Erwin Moller]

Petra Meier schreef:

On Jul 10, 8:03 am, Michael Fesser <[e-mail]:

>.oO(Petra Meier)

>>if I use the following function for all my mySql commands in php, am I
protected against all SQLinjections and XSS attacks?
function sanitize($value){ return
mysql_real_escape_string(htmlspecialchars($value )); }

htmlspecialchars() is not meant for sanitizing data input. You should
only apply it on output.

Additionally SQL injection and XSS are two entirely different things,
which don't have anything in common. To avoid XSS every user-submitted
data which is printed to an HTML page again should be passed through
htmlspecialchars(). This not only applies to data from a database, but
also to form submissions like a search form for example.

>>and i.e.
$query = "insert into users set username='".sanitize($username)."'";
and
$query = "select * from users where
username='".sanitize($username)."'";
or do I miss something here?

You could also have a look at PDO for another type of queries, which use
typed parameters (called "prepared statements").

Micha

thanks for your quick reply!
I know that SQLinjections and XSS are different beasts. for that
reason I would use
mysql_real_escape_string against SQLinjections (entering javascript
<scripttags would still work here)
and
htmlspecialchars against XSS (does not cope all possible SQLinjection
attempts)

and for convenience I'd like to use them nested.
Thanks for the hit to look at PDO, I will for future projects, yet
with this one I have to deal with legacy code and don't want to recode
much.

So still the question if I let all values run through both methods
when doing CRUD on mysql, is there still a security hole?

Hi,

Hi,

I do seriously not want to be pedantic, but here we go:

Are you sure you are using the right words here?
CRUD: Create (=insert), R(ead) (=SELECT), U(pdate), D(elete).
You are only running your 2 methods on the Create, and probably also
when you Update. You do not use them for Delete or Select I expect.
Try to avoid jargon especially when is inappropriate jargon.

Futhermore: If you ask "is there still a security hole", you don't
really appreciate what security is all about. For starters, how can we
tell what securityholes you might be having if we don't know the first
thing about your application, the OS it is running on, the PHPversion
you are using, etc. etc.

I have seen this attitude many times, and for your own sake I want to
warn you for it.
What attitude?
This one: "I heard of security, and some things about SQL injection,
XSSattacks, but I do not have the time to investigate them seriously, so
I made a function I call 'sanitize()' that I call whener I need 'security'."
I see that attitude on a regular basis, and the funpart is they all call
it sanitize() as if that solves your securitytroubles.
It doesn't.
You must make sure you dive into the gory details yourself. Try to hack
your own application. Understand each and every function you are using
and understand WHY you use it. If you are serious about it, you must
also dive into the gory details of the OS you are using.
That is the only way to make a remotely secure application.
Not a function called 'sanitize()'.

Regards,
Erwin Moller

>
thank you
pm

[JuergenRiemer]

On Jul 10, 10:45*am, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.comwrote:

Petra Meier schreef:
Hi,

I do seriously not want to be pedantic, but here we go:

Are you sure you are using the right words here?
CRUD: Create (=insert), R(ead) (=SELECT), U(pdate), D(elete).
You are only running your 2 methods on the Create, and probably also
when you Update. You do not use them for Delete or Select I expect.
Try to avoid jargon especially when is inappropriate jargon.

I used "i.e." in my first post

create: $query = "insert into users set username = '".$username."'";
read: $query = "select username from users where id=".$uid;
update: $query = "update users set username='".$username."' where id=".
$uid;
delete: $query = "delete from users where id=".$uid;

>
Futhermore: If you ask "is there still a security hole", you don't
really appreciate what security is all about. For starters, how can we
tell what securityholes you might be having if we don't know the first
thing about your application, the OS it is running on, the PHPversion
you are using, etc. etc.

I have seen this attitude many times, and for your own sake I want to
warn you for it.
What attitude?
This one: "I heard of security, and some things about SQL injection,
XSSattacks, but I do not have the time to investigate them seriously, so
I made a function I call 'sanitize()' that I call whener I need 'security'."
I see that attitude on a regular basis, and the funpart is they all call
it sanitize() as if that solves your securitytroubles.
It doesn't.
You must make sure you dive into the gory details yourself. Try to hack
your own application. Understand each and every function you are using
and understand WHY you use it. If you are serious about it, you must
also dive into the gory details of the OS you are using.
That is the only way to make a remotely secure application.
Not a function called 'sanitize()'.

perhaps I was not clear enough, so here is my question again:

I have a php script that produces SQL strings (like the 4 ones above).
Even with my level of IT knowledge I know that the variables are entry
points for all sorts of malicious attacks such as SQL injections and
bad data that when printed out to the webpage will execute client side
scripts. so I validate (sanitize) these variables to close this hole.
My humble question was if with the following method calls

$savevalue =
mysql_real_escape_string(htmlspecialchars($unsafev alue));

I achieve this goal? Or is there a better way to check/validate/
sanitize user input?

I also do know that security as such encompasses a lot more issues,
yet this was not my question.

thanks
pm

[Petra Meier]

sorry, I was reading your reply on my colleagues machine with this
google account hihi (sorry J$B!j(Brgen *gg*)
pm

[Jerry Stuckle]

JuergenRiemer wrote:

On Jul 10, 10:45 am, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.comwrote:

>Petra Meier schreef:
Hi,

I do seriously not want to be pedantic, but here we go:

Are you sure you are using the right words here?
CRUD: Create (=insert), R(ead) (=SELECT), U(pdate), D(elete).
You are only running your 2 methods on the Create, and probably also
when you Update. You do not use them for Delete or Select I expect.
Try to avoid jargon especially when is inappropriate jargon.

I used "i.e." in my first post

create: $query = "insert into users set username = '".$username."'";
read: $query = "select username from users where id=".$uid;
update: $query = "update users set username='".$username."' where id=".
$uid;
delete: $query = "delete from users where id=".$uid;

>Futhermore: If you ask "is there still a security hole", you don't
really appreciate what security is all about. For starters, how can we
tell what securityholes you might be having if we don't know the first
thing about your application, the OS it is running on, the PHPversion
you are using, etc. etc.

I have seen this attitude many times, and for your own sake I want to
warn you for it.
What attitude?
This one: "I heard of security, and some things about SQL injection,
XSSattacks, but I do not have the time to investigate them seriously, so
I made a function I call 'sanitize()' that I call whener I need 'security'."
I see that attitude on a regular basis, and the funpart is they all call
it sanitize() as if that solves your securitytroubles.
It doesn't.
You must make sure you dive into the gory details yourself. Try to hack
your own application. Understand each and every function you are using
and understand WHY you use it. If you are serious about it, you must
also dive into the gory details of the OS you are using.
That is the only way to make a remotely secure application.
Not a function called 'sanitize()'.

perhaps I was not clear enough, so here is my question again:

I have a php script that produces SQL strings (like the 4 ones above).
Even with my level of IT knowledge I know that the variables are entry
points for all sorts of malicious attacks such as SQL injections and
bad data that when printed out to the webpage will execute client side
scripts. so I validate (sanitize) these variables to close this hole.
My humble question was if with the following method calls

$savevalue =
mysql_real_escape_string(htmlspecialchars($unsafev alue));

I achieve this goal? Or is there a better way to check/validate/
sanitize user input?

I also do know that security as such encompasses a lot more issues,
yet this was not my question.

thanks
pm

As Micha tried to tell you. htmlspecialchars() is used on OUTPUT. It
has no business being used on database data.

The fact that "for convenience you'd like to use them nested" is
immaterial. They are two different functions with two entirely
different uses.

And as Erwin told you - no, it does not protect against SQL injection.
There are other things to be concerned with, also. You need to read up
and understand security. It's not a topic which can be covered here in
a few usenet messages.

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

[Erwin Moller]

JuergenRiemer schreef:

On Jul 10, 10:45 am, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.comwrote:

>Petra Meier schreef:
Hi,

I do seriously not want to be pedantic, but here we go:

Are you sure you are using the right words here?
CRUD: Create (=insert), R(ead) (=SELECT), U(pdate), D(elete).
You are only running your 2 methods on the Create, and probably also
when you Update. You do not use them for Delete or Select I expect.
Try to avoid jargon especially when is inappropriate jargon.

I used "i.e." in my first post

create: $query = "insert into users set username = '".$username."'";
read: $query = "select username from users where id=".$uid;
update: $query = "update users set username='".$username."' where id=".
$uid;
delete: $query = "delete from users where id=".$uid;

>Futhermore: If you ask "is there still a security hole", you don't
really appreciate what security is all about. For starters, how can we
tell what securityholes you might be having if we don't know the first
thing about your application, the OS it is running on, the PHPversion
you are using, etc. etc.

I have seen this attitude many times, and for your own sake I want to
warn you for it.
What attitude?
This one: "I heard of security, and some things about SQL injection,
XSSattacks, but I do not have the time to investigate them seriously, so
I made a function I call 'sanitize()' that I call whener I need 'security'."
I see that attitude on a regular basis, and the funpart is they all call
it sanitize() as if that solves your securitytroubles.
It doesn't.
You must make sure you dive into the gory details yourself. Try to hack
your own application. Understand each and every function you are using
and understand WHY you use it. If you are serious about it, you must
also dive into the gory details of the OS you are using.
That is the only way to make a remotely secure application.
Not a function called 'sanitize()'.

Hi again,

perhaps I was not clear enough, so here is my question again:

Yes, you were clear. :-)

>
I have a php script that produces SQL strings (like the 4 ones above).
Even with my level of IT knowledge I know that the variables are entry
points for all sorts of malicious attacks such as SQL injections and
bad data that when printed out to the webpage will execute client side
scripts. so I validate (sanitize) these variables to close this hole.
My humble question was if with the following method calls

$savevalue =
mysql_real_escape_string(htmlspecialchars($unsafev alue));

If you expect an integer (line an id) I simply use:
$userid = (int)$_POST["userid"];

If you expect a string, use something like mysql_real_escape_string().

If you want to protect yourself against XXS (or Javascript in general),
you have at least 2 sensible options:
1) call the appropriate functions when you DISPLAY the data, like Jerry
pointed out. So store it 'raw' in the database, and when you pull it out
later for display in a webpage, pass them through the appropriate
functions, like htmlspecialchars().
2) Strip all javascript (or all HTML markup, or whatever suits your
needs) BEFORE inserting it into the database.

Why do we advise you NOT to store your strings in a database with
htmlspecialchars?
Because you are storing something else than the original data. You store
processed data: processed for use in HTML.
It is just more 'natural' to store the original data, and process it the
right way at the right place.

This might seem like a unimportant point maybe, but think about an
export you might need in the future (CSV or XML or whatever) that needs
the original plaintext. Then you are stuck with your html-charaters
instead of original data.
Or a search for certain characters in the table.
(Of course you could convert them back).

In the end it is of course your own choice. :-)
Most programmers I know prefer having 'real data' in the database, and
process it when needed in a way that is apropriate for that task. So
only use htmlspecialchars when you produce the actual html.

>
I achieve this goal? Or is there a better way to check/validate/
sanitize user input?

If sanitize means no SQL attacks, then yes.
If you want to prevent XSS, I think yes, but I am not 100% sure.
(I am not 100% sure because I have just been reading about sofisticated
XSS vectors. So I won't give you advise.)
But this is good place to start reading.
http://en.wikipedia.org/wiki/Cross-site_scripting

>
I also do know that security as such encompasses a lot more issues,
yet this was not my question.

That's OK.
I/we just want to warn you for a false sense of security. :-)

Good luck and regards,
Erwin Moller

>
thanks
pm