Hi Rodolfo

We should probably write this as an article for the UT Magazine. You can translate it to your language.

The article says this clearly:

"Essentially, if a parameterized query is run, and the user supplied paramters are passed safely to the query then SQL injection is typically impossible."

Then goes on to make an incorrect conclusion. String scrubbing is not required. Look at the differences in these VFP queries:

lcVar = "SomeUDF()"
select * from table where name = m.lcVar

compare that to:

lcVar = "SomeUDF()"
select * from table where name = &lcVar.

If we wrote queries that way, we would be asking for trouble! That's the kind of flawed design SQL Injection Attacks seek to abuse. SQL Injection Attacks assume that the programmers and database administrators are building the SQL command as a string and concatentating the user's input directly.

The first query does not execute the same way. m.lcVar in the first one is just a memory variable. VFP executes this:

select * from table where name = "SomeUDF()"

and of course nothing is found. No matter what you put in lcVar it will never be executed! This is really fundamental stuff!

The second one does try to execute SomeUDF(). Why? Because the command VFP executed is this:

select * from table where name = SomeUDF()

SQL Injection Attacks assume the programmers (ALL OF US) write queries in the SQL equivalent of the second form.

lcSQL = "select * from table where name = " + m.lcVar
SQLExec(lnConnection,m.lcSQL)

That entire string will be executed resulting in something like this:

select * from table where name = SomeUDF()

while this

lcSQL = "select * from table where name = ?lcVar"
sqlexec(lnConnection,m.lcSQL)

will cause SQL Server to execute the equivalent of this...

lcVar = "SomeUDF"
select * from table where name = @lcVar

It will not execute this:

select * from table where name = SomeUDF()

If you create your query with parameters you won't have to escape or scrub the user input. This whole topic has become a kind of paranoia. People are scrubbing strings because they were told to.

>Mike,
>
>I found a good paper about SQL Injection Attack.
>http://www.ngssoftware.com/papers/advanced_sql_injection.pdf
>
>I just added the link here for future reference!
>
>
>>As for SQL Injection Attacks, if you pass user input as parameters, that input CAN NOT be used for an attack.
>>
>>lcVar = "un-edited un-scrubbed raw user input"
>>sqlexec(lhConnection,"select * from tblTest where test_name = ?lcVar")
>>
>>No matter what you put in lcVar, there can be no SQL Injection Attack.
>>
>>The profiler will show this:
>>
>>exec sp_executesql N'select * from tblTest where test_name = @P1 ', N'@P1 varchar(21)', ''''';DROP TABLE tblTest'
>>
>>which means find records that have test_name = to what the user entered. It does not mean execute what the user entered.
>>
>>HTH