Re: Prevent Commands in SQL Update

The is a PHP application.

Rich...

"Bob Barrows [MVP]" wrote:

rpotash wrote:
I am cleaning-up a Web application in for someone.

What type? ASP? ASP.Net? Something else?

Is there a login
screen which requests a UserName and Password. There is no field
validation as such in the login screens, so it is possible to put in
as a username "';delete * from user" which would then be passed as
arguments to the SQL query "select userid from user where
user='<username>' and password='<password>'. Since there are a number
of data fields across the application, does anyone know of a sinple
way to clean an SQL statement.

There is no simple way to clean it. You need to use the builtin string
functionality of whatever language you are using in your server-side code to
search the inputs for "bad" words (perhaps using regular expressions) and
reject the inputs when the "bad" words are found. Server-side validation of
ALL user input MUST be done, not only for the SQL Injection
(http://mvp.unixwiz.net/techtips/sql-injection.html) problem you are worried
about, but also for other types of hack attempts (cross-site scripting,
etc).

The problem is, data validation is not enough. Hackers have plenty of ways
of foiling validation schemes (they are usually ahead of the "curve"), and
sometimes, the "bad" words can legitimately be found in user inputs.

So sure, go ahead and clean it up, but the only way to be sure of defeating
sql injection is to quit using dynamic sql. I don't know what database you
are using so I'm going to post my Access and SQL-related posts on my
preferred methods of executing queries via ADO:

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl



SQL Server:

http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&;


New links to code generators:
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip
http://common.mvps.org/barrowsb/DotNet_sp_code_generator.zip


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



.

Relevant Pages

  • Re: Prevent Commands in SQL Update
    ... I am not familiar with PHP, but ADO is ADO ... ... sinple way to clean an SQL statement. ... Server-side validation of ALL user input MUST be done, ...
    (microsoft.public.data.ado)
  • Re: Prevent Commands in SQL Update
    ... validation as such in the login screens, so it is possible to put in ... way to clean an SQL statement. ... ALL user input MUST be done, not only for the SQL Injection ...
    (microsoft.public.data.ado)
  • Re: simple (?) question about having ";" in the password
    ... sanitise input by allowing special characters, ... Attempting to escape SQL strings is a non-starter. ... (BTW, remember that if you are attempting to sanitise input "by hand", then ... of validation to the logic before using the prepared statement. ...
    (comp.lang.java.programmer)
  • Re: COBOL/DB2 Date edit question
    ... Stored procedures are becoming a way of life on many sites, ... Banging away at databases with primitive SQL is ... Within all of this a lot of validation has been going on. ... and submit them together to an UPDATE that triggers the cross validation. ...
    (comp.lang.cobol)
  • navigation and validation on continous form
    ... I am writing a continous form and I want to do validation on user input for ... and could also advise me a better way of doing such validation in continous ... 'Set focus here because the prev it throws error saying that to access the ...
    (microsoft.public.access.formscoding)