Joey said:
Thanks again.
Ok, so by adding this:
set rs=createobject("adodb.recordset")
conn.sp_CheckLogin email,password, rs
instead of this:
sql="sp_CheckLogin '" & email & "','" & password & "'"
set rs=conn.execute(sql)
makes it secure?? If so, I have done it and it works. Just confirming
so I can go thru my other login pages.
Yes. Try the Pedant's simple example. Because this technique I suggested
(commonly called the "procedure-as-connection-method technique) passes
values by parameters, the query engine will not attempt to parse and execute
the injected sql. Here is the difference:
When you cobble together a sql statement by concatenating separate strings
together and pass it to a database, you are leaving it up to the database to
treat it the same way it would handle any statement or set of batched
statements handed to it: i.e., it parses the statement(s), and if valid,
compiles a plan and executes the statements, no matter what destructive
actions they perform - hey, you handed this statement to it - you must know
what you are doing, right?
When using parameters on the other hand, you are handing the database a
"tokenized" sql statement or a stored procedure to execute, along with
values for it to substitute for the tokens (or pass as parameters to the
procedure). So it only parses and compiles a plan (if one is not found in
cache) for the sql statement, and then substitutes the tokens with the
supplied values (or passes the values as arguments to the procedure), making
no attempt to parse or execute those supplied values.
So yes, using parameters will foil all primary sql injection attempts.
But you need to go beyond that. If you do not validate all user inputs, you
will wind up with data filled with sql injection attempts. And if you use
that data in dynamic sql in oter places, then you will wind up accomplishing
the hacker's purpose yourself. This is known as "secondary sql injection".
Also, the procedure that was written for you properly uses the parameters in
the sql statement it executes:
WHERE email=@Email AND Password=@Password
See? it's a tokenized sql statement, so passed parameter values will not be
executed.
If, on the other hand, the person who wrote the procedure did something like
this:
set @sql = 'select ... where email=''' + @email + ''''
execute(@sql)
Then you are back at square one: any sql injected into that @email parameter
value will be parsed and executed.
So, when I say that dynamic sql should be avoided, I mean to say it should
be avoided everywhere: in client code and in stored procedure code.
There are situations where dynamic sql cannot be avoided (when you need to
specify column or field names dynamically). In these situations, proper
validation is essential. You must reat the passed data and verify it
contains nothing untoward BEFORE using it in dynamic sql.
