The Case For Stored Procedures

I used to be a big fan of Stored Procedures. They kept me from worrying too much about SQL Injection and kept my code from getting too repetitive.

That's not to say I'm no longer a fan of Stored Procedures. I still love them, and still use them when the need arises. The thing is, I no longer overuse them. Yes, it's possible to rely too much on Stored Procedures. As I'm very fond of saying - there are no blanket solutions in this business. There's a time and place for everything. And Stored Procedures do have their place.

All the example code below is in PHP and SQLServer.

What's so special about Stored Procedures? 

Security? After all, Stored Procedures protect against SQL Injection, don't they? Yes, they do - to a point. However, parameterized queries do the same thing. In fact, the principle is exactly the same. Whether or not you use Stored Procedures, you should always use parameterized queries.

How about speed? Stored Procedures get compiled ahead of time and all that, so there's less time spent on re-parsing the query with repeated use. Well, you get pretty much the same thing with Prepared Statements.

Ease of maintenance? There's something to be said for that. Separating your logic into different tiers could accomplish that. But this is contentious. Sometimes you want all your logic to be in the same layer, ie your code.

With all of the above in mind, I'm going to outline exactly what Stored Procedures can do, that can't be done any other way.

Security 

Forget SQL Injection for a moment. What Stored Procedures do is execute queries on the tables in your database. With this, you can disable SELECT, UPDATE, DELETE access to all your tables, and grant only access to Stored Procedures. You can even specify which users get access to which Stored Procedures. This greatly limits what users can do to the database. And from a security point of view, that is unambiguously a good thing. A hacker can't simply insert an UPDATE or DELETE or even a SELECT statement into your database if all of these permissions are disabled. Only Stored Procedures are allowed, and since these Stored Procedures carry out very specific functions, they can't be used to attack your database.

Of course, if you're suicidal enough to write a Stored Procedure that drops all your tables, all bets are off.

Again, security

Look at this example...

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$x="1";
$y="y";
$z="abc";

$strsql="UPDATE table1 SET field3 =? WHERE field1 =? AND field2=?";
$statement = sqlsrv_prepare($dbConn,$strsql,array(&$z,&$x,&$y));
sqlsrv_execute($statement);
?>

And look at this one calling a Stored Procedure. Unlike the first example, the tables and fields are not exposed to the application programmer. If you're not the database administrator, you don't know that the table you're updating is table1, or that the fields are field1, field2 and field3. You only know you're executing a Stored Procedure named sp_update!

This is excellent if you have different people working on the database and different people working on the server-side code, and want to limit knowledge of the database to a need-to-know basis.

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$x="1";
$y="y";
$z="abc";

$strsql="CALL sp_update(?,?,?)";
$statement = sqlsrv_prepare($dbConn,$strsql,array(&$z,&$x,&$y));
sqlsrv_execute($statement);
?>

Well, if you use Stored Procedures, there's no longer a need to know.

Neatness 

Logical branching statements are possible in Stored Procedures. And this could be vital if you're a big fan of keeping your code lean. Take, for example, adding and updating a record in a table. Let's say your table named tb_students, and contains student information. Normally, one would have an UPDATE statement for updating the table and a INSERT statement for creating a record, like so. This assumes the variable id was obtained from a POST.

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$name="John Smith";
$address="23 King George's Avenue";
$id=intval($_POST["id"]);

if (id==0)
{
    $strsql="INSERT INTO tb_students (name,address) VALUES (?,?)";
    $statement = sqlsrv_prepare($dbConn,$strsql,array(&$name,&$address));
}
else
{
    $strsql="UPDATE tb_students SET student_name =?, student_address=? WHERE student_id =?";
    $statement = sqlsrv_prepare($dbConn,$strsql,array(&$name,&$address,&$id));
}

sqlsrv_execute($statement);
?>

But wouldn't this be neater?

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$name="John Smith";
$address="23 King George's Avenue";
$id=intval($_POST["id"]);

$strsql="CALL sp_updatestudents(?,?,?)";
$statement = sqlsrv_prepare($dbConn,$strsql,array(&$name,&$address,&$id));

sqlsrv_execute($statement);
?>

You could just call the Stored Procedure sp_updatestudents. If creating a record, just pass a "0" into the student_id field, and if updating an existing record, pass the student's id into the student_id field.

CREATE PROC [sp_updatestudents]
    @name VARCHAR(50),
    @address VARCHAR(150),
    @id INT
AS
    IF @id=0
        INSERT INTO tb_students (name,address) VALUES (@name,@address)
    ELSE
        UPDATE tb_students SET student_name =@name, student_address=@address WHERE student_id =@id;
GO

 

Portability between server-side languages 

Imagine if your code was in PHP and you wanted to make a switch to, say, C#. Hey, if your database calls were all in Stored Procedures, no problemo. Whether you're using PHP or C#, you still call the same Stored Procedure. The Stored Procedure still performs the exact same function on your database! But if your database calls were actually in the code itself, now you'd have to change all of them from PHP to C#.

Ouch.

Are Stored Procedures invincible? 

Not by a long shot. There are situations where use of Stored Procedures makes things sketchy (though we won't go into that here). But the reasons above are the strongest ones I have for using them. Your mileage may vary!

Thanks for reading. Stay tuned for the next UPDATE!
T___T