adodb or sql procedure ? 1

[baran121]

Hi i want to learn which one is better to use ADODB or sql procedure?
there is code below explain it.
thank you.


ADODB connection ;

rs.open "select * from mytable",cnn

sql procedure ;

With Cmd
.CommandType = adCmdStoredProc
.CommandText = "mytable_list"

Set .ActiveConnection = cnn
Set rst = .Execute
End With

Set Cmd = Nothing

 

[gmmastros]

This relatively simple question ha a lot of answers.

Performance
From a performance perspective, both methods will be approximately the same. You would probably have a hard time measuring the difference in execution time.

User Permissions
You can control security better by using stored procedures. If you deny a user access to the table, you can still grant them execute on the stored procedure even though the stored procedure uses the table.

Program Maintenance
By using stored procedures, you can make a change to the stored procedure and distribute the procedure (or put it in to production) without needing to recompile (and test) the application. For your simple example, it probably won't make much difference, but for more complicated queries it might. For example, if you joined multiple tables together and you wanted to change a left join to an inner join, you would need to recompile the application and distribute it to all the users. If you used a stored procedure, you simply change that one procedure, test it, and then apply it to the database. All the existing applications wouldn't need to change.

Security/[google]SQL Injection[/google]
When you use a command object with parameters (for more complicated queries), you effectively prevent SQL injection. When you build your SQL in the application, the possibility exists for SQL Injection.

Length of code
I've never been a fan of counting lines of code. I think it is relatively meaningless. The first method requires less code than the second method, but the second method has a lot of other advantages so I think the extra code is worth it.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

@gmmastros,

How would you best describe what SQL injection is and why is it important?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Andrzejek]

Click here to find out.

Have fun.

---- Andy

 

[gmmastros]

SQL Injection is when people try to hack the database to get data that they should not have access to, or deliberately try to sabotage it.

The way it works is this....

Suppose you have a login box on a website. People are expected to type in their user name and password. In the code, you have something like this...

[tt]SQL = "Select * From Users Where UserName = '" & txtUserName.Text & "' and Password = '" & txtPassword.Text & "'"[/tt]

You then open a recordset and check to see how many rows are returned. 0 rows means the user name or password is incorrect. 1 row means the username and password are valid. Simple, right?

Well.... suppose the "Bad hacker guy" uses values like this (without the quotes):

username = "Blah"
password = "'; Drop Table Users;"

When you build your SQL string, you end up with:


[tt]SQL = "Select * From Users Where UserName = 'Blah' and Password = ''; Drop Table Users;'[/tt]

Or:

[tt]Select * From Users Where UserName = 'Blah' and Password = ''; [!]Drop Table Users;[/!]'[/tt]

The first select part wouldn't return any rows, but the DROP TABLE USERS would still be executed. Suddenly you have lost your users table and nobody can log in to your application any more.

Alternatively, the "Bad Hacker Guy" could use these values:

Username = "Blah"
Password = "' or 1=1;"

You SQL would look like this:


[tt]SQL = "Select * From Users Where UserName = 'Blah' and Password = '' or 1=1;'[/tt]

The "or 1=1" part would cause the query to return all the rows. Since there are rows, your app may interpret this as a successful login and now this guy has access to your application and all your data.

The simplest way to protect yourself from SQL Injection is to use the command object with parameters. Given the above scenarios, the attempt by the hacker to destroy your data and/or gain access to your data would not happen because the command object doesn't just build a SQL String. Behind the scenes it is parameterizing the query and the entire SQL Inject hacky stuff would be passed in for the password. It wouldn't match so the user would not gain access to your data. The DROP TABLE part would be seen as the password, not as additional SQL code to run.

Does this make sense?

I encourage you to read more about this: [google]SQL Injection[/google]



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Sorry for hijacking this thread.

Thank you for the replies regarding SQL injection. I had heard about it and tried to read up on it, in the same way that Andy had pointed out, but I found that a lot of the descriptions were poorly written and somewhat difficult to comprehend.

Thank you for making my understanding of the subject clearer.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[baran121]

thank you gmmastros, thank you very much...