Out of curiosity I asked another friend, a team lead, how things where going with their PHP MySQL project, for which they had planned to have most of their business logic in stored procedures. I got an email in reply stating something along the lines: "Our developers found that mysqli does not support stored procedures correctly. We use PDO.". Well, the existing documentation from PHP 5.0 times is not stellar, I confess. But still, that's a bit too much... it ain't that difficult. And, it works.

He describes the three parameters (IN, OUT and INOUT) and gives some examples of setting/getting them from your SQL statements. They're all still set up using the query method on your connection as well as handling the result sets that come back and working with prepared statements.

Last week I had an interesting conversation on twitter about the usage of stored procedures in databases. Someone told stored procedure are evil. I'm not agree with that. Stored procedures are a great place to store business logic. In this post I'm going to test the performance of a small piece of code using stored procedures and using only PHP code.

In the end, the results showed that the stored procedures method was actually faster and used a bit less memory than the normal "plain PHP" method. It can be a bit more difficult to use than just a SQL statement in a string (properly escaped, of course) but can be worth the extra hassle when you need that performance boost.

When I've asked people about their strategies for preventing SQL injection, one response is sometimes "I use stored procedures." But, stored procedures do not, by themselves, necessarily protect against SQL injection. The usefulness of a stored procedure as a protective measure has everything to do with how the stored procedure is written. Write a stored procedure one way, and you can prevent SQL Injection. Write it another way, and you are still vulnerable.

The short answer is "not always" but he gets into a more detailed answer with a sample login script and the SQL to create the stored procedure the "wrong way" (using the value dynamically in the SQL of the procedure) and the "right way" (assigning them directly like bound variables).

Are you writing Stored Procedures if not please take a look at this post. Stored procedures can help to improve web application performance and reduce database access traffic. In this post I want to explain how to create and call the stored procedures from database server.

He shows you how to create a sample table and stored procedure on the server side (selecting a username from a users table) and how it compares to a normal SQL query. Two different ways to input values into the procedure are shown as well.

Calling PL/SQL code from PHP can be tricky sometimes, specially when the PL/SQL procedure has input and output parameters. In this posting I will show you how to call a procedure from the PHP using Zend Framework. I will assume you have some experience using Zend Framework, specially the Database module (Zend_db).

He walks through the creation of a simple PL/SQL stored procedure and how to prepare your query to get results out of it (Zend_Db_Statement_Oracle and an execute call). There's a few stipulations you'll need to follow - named parameters, reserving space for the output and using references for output variables.

Alan, I think you were smoking way too much PHP when you wrote this post.. This in particular really surprised me to hear you say [that a module that made mysql stored procedure calls based on a URL and returned JSON could make PHP obsolete]. While I do understand the concept your explaining, I simply can't see how the model is practical at all for two big reasons.

His reasons involve not having a business case where an entire application is right there for the user to download and that its an insecure method for running an app.

It would be really useful to have an easy way to pass arrays as bound parameters to queries or procedures from PHP. This would be especially useful if you're letting Oracle handle most of your data manipulating (as you should).

She includes an example of how she's like it to work. Sadly, it doesn't but there are some ways that a developer could get close. Here's her process:

• No queries in loops, please!
• In the ideal world...
• Put all your DML in stored procedures.
• str2tbl
• The list_pkg package
• list_pkg in your procedure
• list_pkg in your PHP
• Leveraging list_pkg

The list_pkg is based around this article from AskTom.

Now that we become familiar with the fundamentals of stored procedures it is time to start playing with the "Big Boy Toys". This article will go over stored procedures's built in error handling, the security features available, various "extras" available, what isn't allowed in a stored procedure, and some basic administration of the stored procedures. So lets stop talking and bust open the toy box and start playing!

Since they've already gotten the groundwork laid in the previous article, they jump right into the transactions in this part. In this example, they create a "mass insert", show hoe to create some error handling, add in a dash of security, and toss in a few extras. There's also a few small gotchas included to watch out for.

With the release of MySQL5 a bold new world opens up to the PHP developer... the world of a database programmer. In this world the interaction with the data can be done right where the data is located - not in a script that is far far away in a distant server. In this article we will be taking you on a journey that will introduce you to MySQL's stored procedures.

They cover some of the background of transactions and some of the basic concepts behind them. All of the examples provided are done from the command line in this part of the series, but PHP relevant examples should be coming up shortly.

This time, the topic is still a bit up in the air and they request that those attening vote on one of two - either "Ajax tools in Eclipse" or "postgresql + stored procedures" - but from Ray Hunter. The meeting will be in the usual place, the Linux Networx offices, and will get going around 7pm.

For more details on the group and this meeting, check out their website.]]> Fri, 17 Feb 2006 06:49:03 -0600