March 21, 2008 at 10:32 am
I think that is a very good point. From what I have read an exists query is very cheap performance wise.
-least required priviliges
-least required data (collected or retrieved)
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
March 21, 2008 at 10:55 am
Dave: Thanks for the feedback!
March 23, 2008 at 11:19 pm
This is a excellent article and well written too. Very simple and easy to understand. Also thanks to people who added more value to this by commenting.
Regards
🙂
March 24, 2008 at 5:15 am
One more article that doesn't even mention the simpler way of avoiding SQL Injection using both ADO or ADO.Net. This one came closer when it suggested using sp_executesql with parameters, but it implies that the programmer would have to have invoked the procedure explicitly. Both ADO and ADO.Net have built-in mechanisms for invoking sp_executesql (and it doesn't need Enterprise Library or other components). You just need to use SQLParameters.
SQLCommand cmd = new SQLCommand();
cmd.CommandText = "select * from test_logins where username = @username and password = @password";
cmd.Parameters.Add("@username", SqlDbType.VarChar,40);
cmd.Parameters.Add("@password", SqlDbType.VarChar,40);
cmd.Parameters["@username"].Value = Username.Text;
cmd.Parameters["@password"].Value = Password.Text;
The ADO/VB/ASP version is pretty similar.
Notice that, besides avoiding SQL Injection, that approach has many other advantages, such as validating string lengths, aumtomatically converting types, being easier to maintain should a column type change, etc.
It puzzles me how seldom it is mentioned in the forums and articles, and, therefore, how many programmers still concatenate parameters in their queries.
March 24, 2008 at 7:42 am
Why not add a option to sql server to disallow multiple sql statements in one execution. (basically get rid of the ; in ad hoc queries). that would halt many of these attacks in it's tracks
March 24, 2008 at 5:55 pm
This was an awesome article that sums it up very succinctly. It was an excellent review for me and I am sending it to my team as well.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
March 24, 2008 at 10:21 pm
Pablo Emanuel (3/24/2008)
One more article that doesn't even mention the simpler way of avoiding SQL Injection using both ADO or ADO.Net. This one came closer when it suggested using sp_executesql with parameters, but it implies that the programmer would have to have invoked the procedure explicitly. Both ADO and ADO.Net have built-in mechanisms for invoking sp_executesql (and it doesn't need Enterprise Library or other components). You just need to use SQLParameters.SQLCommand cmd = new SQLCommand();
cmd.CommandText = "select * from test_logins where username = @username and password = @password";
cmd.Parameters.Add("@username", SqlDbType.VarChar,40);
cmd.Parameters.Add("@password", SqlDbType.VarChar,40);
cmd.Parameters["@username"].Value = Username.Text;
cmd.Parameters["@password"].Value = Password.Text;
The ADO/VB/ASP version is pretty similar.
Notice that, besides avoiding SQL Injection, that approach has many other advantages, such as validating string lengths, aumtomatically converting types, being easier to maintain should a column type change, etc.
It puzzles me how seldom it is mentioned in the forums and articles, and, therefore, how many programmers still concatenate parameters in their queries.
Isn't this precisely what is meant by using parametrized stored procedures? How else would you call one?
(Well except using a procedure name as command text instead of statement.)
something like:
SQLCommand cmd = new SQLCommand();
cmd.CommandText = "doLogin";
cmd.Parameters.Add("@username", SqlDbType.VarChar,40);
cmd.Parameters.Add("@password", SqlDbType.VarChar,40);
cmd.Parameters["@username"].Value = Username.Text;
cmd.Parameters["@password"].Value = Password.Text;
set rs = cmd.excute
------------
create procedure doLogin
@username varchar(40),
@password varchar(40)
as
if exists (select 1 from test_logins where username = @username and password = @password)
begin
select 1
end
else
begin
select 0
end
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
April 4, 2008 at 11:26 am
AIM48 (3/24/2008)
Why not add a option to sql server to disallow multiple sql statements in one execution. (basically get rid of the ; in ad hoc queries). that would halt many of these attacks in it's tracks
Because it’s too useful for legitimate purposes. Suppose, for instance, you’re INSERTing a new record, and you also need to know the Identity value that was just generated for the new record (e.g. to pass an online registration ID# back to the customer, or to the PayPal payment). The only way to do so reliably is to request it immediately after the INSERT in the same batch of statements:
INSERT INTO myTable (various, fields, as, needed) VALUES ('various', 'data', 'as', 'needed'); SELECT SCOPE_IDENTITY();
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply