[thelist] Select QRY problem

Phil Turmel philip at turmel.org
Tue Mar 15 07:04:54 CST 2005


Ken Schaefer wrote:
> : Pringle, Ron wrote:
[snip]
> : > However, I would have thought it would have thrown a type mismatch
> : error.
> : 
[/snip]
> : Ron,
> : 
> : At the point in your code where you were doing the concatenation, you
> : had your base query as a string constant, and the result of
> : response.querystring.  Both of these are STRINGS.  No type mismatch
> : error is ever going to occur at that point.  Plus, ASP will perform
> : silent type conversion in many expressions, including string
> : concatenation (along with a whole bunch of other scripting languages).
> : 
> : You really need force querystrings into typed variables before they are
> : used anywhere else in your code, especially before they are passed to a
> : SQL driver.  Google "SQL injection" for a good scare.
> 
> He is creating an *SQL* statement. What you are asking him to do is
> irrelevant considering the error. The error is not the database complaining
> about a type-mismatch. The database is complaining about a parameter being
> *missing*. Casting a variable up in the ASP layer is completely irrelevant to
> building an SQL statement. You need to make sure that the *database* can do
> the necessary parsing/conversion, however in your ASP layer, everything will
> still be a string (well, technically a variant since there are no strings in
> ASP)
> 
> Cheers
> Ken

Ken & Ron,

Maybe I wasn't clear.  I was trying to point out that there wouldn't be 
a type mismatch while creating the SQL statement, as that's string 
concatenation.  (No contradiction to Ken there)

However, checking for the presence and proper data type of response 
variables MUST be done BEFORE building the SQL statement, or you are 
vulnerable to SQL injection attacks.

I do this by casting numbers from GET/POST/COOKIE variables into long 
integer or float.  Then concatenate into SQL statement.  If something is 
missing, I'll get a zero, but no SQL syntax error.  And I'm safe from 
deliberately malformed GET/POST contents.

How all this is done varies with language (I use PHP on my webservers), 
but the principle is the same:  SQL Statements that include data from 
web forms or other possibly fudged data must be built from carefully 
validated substrings.  I still recommend y'all google "SQL Injection".

The side benefit of this: never getting a SQL syntax error from bad user 
data.

Phil


More information about the thelist mailing list