MySQL ODBC Connector - Dynamic queries v/s Stored Procs

Author

Dynamic queries v/s Stored Procs

rtroiana

2006-02-28, 8:28 pm

------ =_NextPart_000_0006_
01C63C54.317D9130
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I'm in a process of transitioning from dynamic queries that were generated
in C# code to using Stored Procedures. Although i haven't done any analysis
of time difference between the two. But i do have few reasons to move to
stored procedures:

1) I work on a security application and i heard of SQL injection. This is
one of the most common problems with dynamic queries. But if i use stored
procs, I won't have to worry about Sql injections, since queries in Stored
Procs are precompiled.

2) Stored procedures result in easier maintenance because it is generally
easier to modify a stored procedure than it is to change a hard-coded SQL
statement within a deployed component.

3) Lastly, stored procs add an extra layer of abstraction; i can keep the
database access to storage and retrieval and move most of the business logic
into a middle tier.

Although i have the DB code in place as dynamically generated strings in
code. I might have to do a lot of rework to implement stored procs. Is it
really worth it to move to stored procs?

If it's really a good idea to move my sql queries out of C# code into stored
procs. I've the following problems:

Problem 1:

--------------

I have code in place that generates dynamic query like this:

dbCmd.CommandText = "UPDATE Table1 SET " + setClause + " WHERE ID = " + _id;

Table1 has about 15 fields and on the basis of what field is changed, i
generate the "setclause".

Now if i have to do it in Stored Proc, i have to add a parameter for each
field and update all the fields on the basis of the criteria.

Which would be considered best practice in this case?

Problem 2:

------------

I'm also using dynamic queries in case of SELECT queries.

For example, i have to fetch records from "Table1" on the basis of "ID" or
"Name" or combination of both.

dbCmd.CommandText = "SELECT ID, Name, Field3, Field3, ..., " + whereClause +
" ORDER BY Name;";

a) To implement this in stored proc, i can either have different stored proc
to get data from Table1 on the basis of criteria, or

b) i can have all the criteria fields as parameters and have If statements
to check which one is set and write a sql query for each case.

What would be considered best practice in this case?

Thanks.

------ =_NextPart_000_0006_
01C63C54.317D9130--

rtroiana

2006-02-28, 8:28 pm

Thanks for the answer.

Now let me get this right, are you suggesting using prepared statements from
the C# code? Since I have seen some examples of prepared statements used in
stored procedures too.

I understand stored procedures are not best tool in all situations. But what
if while inserting records, I have certain operations that need to be
executed consecutively. But in case of queries written inside the code, I
have separate database calls and that results in too many database calls.

For example,
1) I insert data in member table and
2) Get the "ID" and
3) Insert a new row in entity table with that "ID".

I can do all that in one stored proc.

Do you think it's a good idea to use stored procedure for certain cases and
may be use prepared statements from my c# code in other.

Lastly, from architecture point of view, is it considered good to have sql
string embedded in C# code?

Thanks,
Reema

-----Original Message-----
From: James Black & #91;mailto:jblack@ie
ee.org]
Sent: Tuesday, February 28, 2006 10:54 AM
To: rtroiana
Subject: Re: Dynamic queries v/s Stored Procs

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

rtroiana wrote:

> Although i have the DB code in place as dynamically generated strings in
> code. I might have to do a lot of rework to implement stored procs. Is it
> really worth it to move to stored procs?

You may want to use prepared statements instead of stored procedures,
as it does protect against sql injection. It obviously is as much work
to change as a dynamic query, but I don't think stored procedures are
the best tool in all situations.

- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black james@usf.edu
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEBHIpikQgpVn8
xrARAxf+AJ9Vj+IsMrTX
AaIYgB3fHrEZPJZv7ACf
du74
flJn+6NSRt5cLZIfRauZ
UhY=
=cUPJ
-----END PGP SIGNATURE-----

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links