Re: RFP2K01 - "How I hacked Packetstorm" (wwwthreads advisory)

[jwhitt () INSIDERMARKETING COM (Jeremy Whittington)]

Hello,

I would like to make a comment on your statment about SQL Syntax and how you
deal with numeric values.

>  If you're stating that you cannot enclose your numeric values in single
>  quotes in SQL query strings, it seems to be incorrect. I'm also using SQL as
>  my backend, and I've ALWAYS enclosed numbers in single quotes, and it has
>  always worked.

When inserting data into a Numeric datatype you do not use single quotes around
the values.

If Field2 was a Numeric datatype in this example it would Fail on MS SQL Server
6.5, 7.0 , MS Access 97/2k, Oracle 6i+, and Dbase.
INSERT INTO Table (Field1, Field2) Vaules('String','1')

The correct way to insert a Numeric value, would be with no quotes.
INSERT INTO Table (Field1, Field2) Vaules('String',1)

I have used MS SQL Server 6.5, 7.0 , MS Access 97/2k, Oracle 6i, and Dbase. All
of them use the same SQL Syntax to deal with numeric values. Mysql will allow
you to do this, however, not everyone is running Mysql. Also I would like to
note that it is not the correct way to do it in Mysql either, it has to take
extra time to convert them. I know thats not the point in this case but, never
the less.

Jeremy Whittington

Jaanus Kase wrote:

> Hi,
>
> > -----Original Message-----
> > From: Bugtraq List [mailto:BUGTRAQ () SECURITYFOCUS COM]On Behalf Of rain
> > forest puppy
> > Sent: 03. veebruar 2000. A. 18:33
> > To: BUGTRAQ () SECURITYFOCUS COM
> > Subject: RFP2K01 - "How I hacked Packetstorm" (wwwthreads advisory)
>
> a nice analysis otherwise, but this caught my eye:
>
> > You see, there are different field types.  You can have
> > strings, boolean
> > values, various numeric values, etc.  While a string field
> > needs to be in
> > the format of field='data', a numeric field doesn't use the '' (i.e.
> > numeric_field='2' is invalid).  The correct syntax for
> > numeric fields in
> > numeric_field=2.  Ah ha!  There's no quotes to deal with, and
> > you can't
> > even use quotes anyways.  The correct solution is to make
>
> If you're stating that you cannot enclose your numeric values in single
> quotes in SQL query strings, it seems to be incorrect. I'm also using SQL as
> my backend, and I've ALWAYS enclosed numbers in single quotes, and it has
> always worked.
>
> Besides, when using PHP as front end, it has the nice AddSlashes and
> StripSlashes functions, which, according to the manual, "Returns a string
> with backslashes before characters that need to be quoted in database
> queries etc. These characters are single quote ('), double quote ("),
> backslash (\) and NUL (the null byte).", and "Returns a string with
> backslashes stripped off. (\' becomes ' and so on.) Double backslashes are
> made into a single backslash.". So it's exactly what we need to "disarm" web
> queries, and pretty much the same as the $dbh->quote thing (though you later
> have to 'decode' the data and strip the backslashes).
>
> I wrote two "wrapper" functions for PHP, which take their input and add or
> strip slashes (recursively if needed) on both arrays and normal variables
> (never mind the funny names, you can change them if you want, but the idea
> should be clear :). I'm sure it could be done more effectively, and in other
> languages too, but I'm not much of a code wizard, so let's stick with these
> for now.
>
> function paneslash (&$sisend) {
>         if (is_array($sisend)) {
>                 while (list($siskey) = each($sisend)) {
>                         paneslash($sisend[$siskey]);
>                 }
>         } else {
>                 $sisend = addslashes($sisend);
>         }
> }
>
> function korjaslash (&$sisend) {
>         if (is_array($sisend)) {
>                 while (list($siskey) = each($sisend)) {
>                         korjaslash($sisend[$siskey]);
>                 }
>         } else {
>                 $sisend = stripslashes($sisend);
>         }
> }
>
> This way, when fetching data from database, you can just say
>
> $my_array=mysql_fetch_array($some_query);
> korjaslash($my_array);
>
> and boom, backslashes are stripped (provided there were any). Prior to
> inserting/updating data, just go through each variable with
> paneslash($varname) and off you go. When performing insert/update, I have
> always used the "update blaah set columnname='value'" syntax with single
> quotes always surrounding, regardless of the columnname type (integer,
> string etc), and it has worked with no trouble.
>
> However, checking numeric data for validity is still a healthy practice,
> since it's likely you'll later pass it to functions (like calculations or
> something) that assume they're dealing with valid numbers - and when
> inserting non-numeric data to columns of numeric type, MySQL seems to
> quietly replace it with zero (at least in case of integers).
>
> PHP has a nice set of is_* functions (like is_array, is_int etc), but they
> seem to be somewhat broken/misdocumented - when using <input type="text">
> and passing the form to PHP script, this variable (before interpreting it
> anyhow) is always assumed to be string, even when it only contains valid
> numeric data. (the is_array in above functions works fine, though, so
> perhaps I've misread something.) Other functions that could be used are the
> GetType and SetType in PHP - when a variable is supposed to be numeric, but
> contains non-numeric data, GetType reports it to be something else than
> integer, and perhaps you can then warn user about this - A.K.A. server-side
> validation, which should be a normal, healthy, common sense programming
> practice anyway ("NEVER trust the user..."). Moreover, it's more secure than
> client-side validation, since one who has malicious intent can always
> construct their own form/query and just delete the validating JavaScript or
> whatever on the client side.
>
> The PHP/MySQL combination is powerful, elegant, and easy to use (as well as
> many other front- and backends). As dynamic page generation is gaining
> momentum and discussion forums, user profile maintenance pages etc are
> popping up all over the place, I'm sure we'll see many more vulnerabilities
> of this type both in custom-written scripts as well as commercial software
> (as the WWWThreads example demonstrated). With software that is both easy to
> use and rich in features, security concerns are often disregarded - many
> entry-level webmasters and script developers are clueless about the above
> possibilities. So let's keep our eyes open, and queries safe.
>
> Regards,
> Jaanus Kase
> Cybernetica (http://www.cyber.ee/)