Some assistance with MS SQL injection and PHP please

[gene.ellis@gmail.com]

Hello everyone,

Put simply we have recently been the victims of a malicious hacker
because we were not aware of the dangers of SQL injection. Now, I am
adding addition validation to my forms and also GET variables (we are
using PHP). Does anyone have any good techniques for the kind of
validation I should be using to avoid SQL injection? I basically want
to create a PHP function, fun any form variables through the function,
and then stop the script from executing if any bad input in found.
Thanks for all of your help. I don't want us to lose all of our data
again!

GE

[Adam Plocher]

Re: Some assistance with MS SQL injection and PHP please

Hi GE, I actually just wrote an article about this but have not yet
published it (it needs some revisions, but the gist of it should be
fine). I will email it to you so you can take a look at it.

[Good Man]

Re: Some assistance with MS SQL injection and PHP please

gene.ellis@gmai l.com wrote in news:1137540834 .595198.106210
@o13g2000cwo.go oglegroups.com:
[color=blue]
> Hello everyone,
>
> Put simply we have recently been the victims of a malicious hacker
> because we were not aware of the dangers of SQL injection. Now, I am
> adding addition validation to my forms and also GET variables (we are
> using PHP). Does anyone have any good techniques for the kind of
> validation I should be using to avoid SQL injection? I basically want
> to create a PHP function, fun any form variables through the function,
> and then stop the script from executing if any bad input in found.
> Thanks for all of your help. I don't want us to lose all of our data
> again!
>
> GE[/color]

well, there are many ways to clean user input, and more than one should
be used at a time.

the first thing i do to ANY user input variable is addslashes(); which
will turn ' into /' and render ineffective any attempt to insert/delete
records from the database. i'm not sure if this

with any data i am expecting to be numerical, i is_numeric(); it, and
toss the user to an ugly error page if its not numeric

also, i rarely ever use anything the user gives me for direct use in my
database. if i need the user to tell me the name of a
column/database/field they need to use for a particular operation, i use
MY short forms/abbreviations, look for them, and then substitute the
right names. ie: in a url "search.php?val ue=416&searchty pe=phone", my
script would say something like...

if($searchtype= ="phone") {
$realquery = "SELECT * FROM TELEPHONES ETC ETC";
}



....instead of putting 'TELEPHONES' directly into the URL itself. by
using my own shorthand/abbreviations for real column names, table types,
or ANYTHING database, I can look out for those variables specifically and
ignore anything that isn't what im looking for. So in your case, mix up
the real form variable names with temporary ones.


I'm sure there are many other tips, but the main theme is: if you can
help it, trust NOTHING you get back from the user.

[Peter Fox]

Re: Some assistance with MS SQL injection and PHP please

Following on from 's message. . .[color=blue]
>Hello everyone,
>
>Put simply we have recently been the victims of a malicious hacker
>because we were not aware of the dangers of SQL injection. Now, I am
>adding addition validation to my forms and also GET variables (we are
>using PHP). Does anyone have any good techniques for the kind of
>validation I should be using to avoid SQL injection? I basically want
>to create a PHP function, fun any form variables through the function,
>and then stop the script from executing if any bad input in found.
>Thanks for all of your help. I don't want us to lose all of our data
>again![/color]

This is covered in the manual. Look for ....you guessed it ... SQL
injection.

BTW You can help yourself by thinking of _all_ the ways your queries
(and data) could be hijacked or made nonsense. For example what happens
if your date of birth to age routine has a bug - do you always validate
_all_ your data or at least do sanity checks - at point of database
storage - not necessarily the raw data?

There are plenty of articles : Google is your friend.

[color=blue]
>
>GE
>[/color]

--
PETER FOX Not the same since the submarine business went under
peterfox@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>

[JT]

Re: Some assistance with MS SQL injection and PHP please

One basic method to prevent SQL Injection is to restrict input to conform to
it's expected format. For example, if UserID is an integer, then it should
not contain alpha characters or symbols. You should also constrain the
passwords (or any user text input) to not include comparison (=, <, >, etc.)
or single / double quote characters.

How To: Use Regular Expressions to Constrain Input in ASP.NET

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000001.asp

I havn't used this personally, but it is possible to make regular expression
calls in T-SQL via the VBScript object, however, the LIKE comparison
operator or patindex() function would be preferred.

http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx

Also, in your programming, instead of this:

if not rs.eof() ...

do this:

if rs.rowcount = 1 and rs[Password] = sPassword ...


<gene.ellis@gma il.com> wrote in message
news:1137540834 .595198.106210@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> Hello everyone,
>
> Put simply we have recently been the victims of a malicious hacker
> because we were not aware of the dangers of SQL injection. Now, I am
> adding addition validation to my forms and also GET variables (we are
> using PHP). Does anyone have any good techniques for the kind of
> validation I should be using to avoid SQL injection? I basically want
> to create a PHP function, fun any form variables through the function,
> and then stop the script from executing if any bad input in found.
> Thanks for all of your help. I don't want us to lose all of our data
> again!
>
> GE
>[/color]

[JT]

Re: Some assistance with MS SQL injection and PHP please

Also, Microsoft has published several patterns & practices documents related
to securing ASP.NET applications on MSDN:

Improving Web Application Security: Threats and Countermeasures

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/ThreatCounter.asp

Threat Modeling Web Applications

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/tmwa.asp

Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetlpMSDN.asp

"JT" <someone@micros oft.com> wrote in message
news:uowNw9EHGH A.2036@TK2MSFTN GP14.phx.gbl...[color=blue]
> One basic method to prevent SQL Injection is to restrict input to conform
> to it's expected format. For example, if UserID is an integer, then it
> should not contain alpha characters or symbols. You should also constrain
> the passwords (or any user text input) to not include comparison (=, <, >,
> etc.) or single / double quote characters.
>
> How To: Use Regular Expressions to Constrain Input in ASP.NET
> http://msdn.microsoft.com/library/de...aght000001.asp
>
> I havn't used this personally, but it is possible to make regular
> expression calls in T-SQL via the VBScript object, however, the LIKE
> comparison operator or patindex() function would be preferred.
> http://blogs.msdn.com/khen1234/archi...11/416392.aspx
>
> Also, in your programming, instead of this:
>
> if not rs.eof() ...
>
> do this:
>
> if rs.rowcount = 1 and rs[Password] = sPassword ...
>
>
> <gene.ellis@gma il.com> wrote in message
> news:1137540834 .595198.106210@ o13g2000cwo.goo glegroups.com.. .[color=green]
>> Hello everyone,
>>
>> Put simply we have recently been the victims of a malicious hacker
>> because we were not aware of the dangers of SQL injection. Now, I am
>> adding addition validation to my forms and also GET variables (we are
>> using PHP). Does anyone have any good techniques for the kind of
>> validation I should be using to avoid SQL injection? I basically want
>> to create a PHP function, fun any form variables through the function,
>> and then stop the script from executing if any bad input in found.
>> Thanks for all of your help. I don't want us to lose all of our data
>> again!
>>
>> GE
>>[/color]
>
>[/color]