How to use stores procedures in mysql

SQL injection protection occurs at the language level (like PHP), rather than the database layer.

https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php provides a good PHP overview of how to design SQL injection blocking into your code base.

To ensure you code is crafted well, use http://sqlmap.org/ to test your code.

You'll use the PHP as an example, then map the PHP approach to your ASP code base.

Thanks David! Im at a family dinner right now, but will check your answer once I get an opportunity. I'll most likely need to know how to invoke the stored procedure in classic asp.

Check out this article. Although written specifically for SQL Server, if written correctly, a stored procedure will prevent injection. Using parameterized stored procedures is the recommended way of coding your apps, rather than building the SQL in the app and passing it to the server.

I really appreciate the feedback guys, but I'm afraid my skills are not quite at that level. I wasn't clear enough in the original post.
I wouldn't say those links you have provided are written in Greek, German is more like it - but that still means that I don't understand enough of it to create my own code.

What I need is an example of how to create a stored procedure based on the database contents provided above and a corresponding code in classic asp showing me how to get that data from the stored procedure.
I know it's asking a lot, but I've scoured the internet (maybe badly) and can't find what I'm looking for.

You create the procedure with the syntax:

create procedure usp_my_proc (parameterName1 parameterType1[, parameterNameN, parameterTypeN]) )
begin
    ...
end

Select allOpen in new window

There are quite a few pages available on the web to explain how to create stored procedures in MySQL. Check out this one and here for starters. Search in Google for MySQL Stored Procedures for more information.
Once you get to writing a procedure or two and get comfortable with that part, if you run into issues let us know.

David is correct (to a degree) that SQL Injection is handled at the application level, but that is either through the use of parameterized queries or stored procedures. I like using stored procedures because, if you need to make some change at the database level, where stored procedures live, you don't need to make any changes to your application layer.

My take is, code that affects the database should live, as much as possible, in the database (i.e. all data access is through stored procedures). Code that affects the application should live, as much as possible, in the application.

Different developers may feel differently, and I'm not saying this way is the only, or even necessarily always the right way, it is just one way. Getting off the subject for a minute... many developers will format data in the database layer.
For example:

SELECT FIRST_NAME + ' ' + MID_INIT + '. ' + LAST_NAME AS FullName, 
       LEFT(SSN, 3) + '-' + SUBSTRING(SSN, 4, 2) + '-' + RIGHT(SSN, 4)  AS SSN
       '(' + LEFT(PHONE, 3) + ') ' + SUBSTRING(PHONE, 4, 3) + '-' + RIGHT(PHONE, 4) AS PhoneNumber
FROM   myTable

Select allOpen in new window

instead of just returning FIRST_NAME, MID_INIT, LAST_NAME, SSN and PHONE and letting the presentation layer format the data. Many will tell you that the presentation layer should do your formatting; for one thing, there may be locale/language differences that need to be taken into account. The database server may reside in the U.S. but you have a web application pulling data from Japan, the U.K. India and Italy. The database does not necessarily know where the data request is coming from, but the application that is running does.

Hi Doug,
I'm using Jquery mobile which forces me to format the data after it leaves the database (I think).

Question: Does stored procedures put more stress on the database than my conventional classic asp connection approach?

I have a Status field and a listname field. My first page filters my the view so it only contains people with a certain status on a certain list (i.e. 'Active' on 'List B'). This is controlled by dropdown menus on the presentation layer (if this then that kind-of-thing). Would I need a stored procedure pr. possibility (i.e. 'Inactive', 'List B' and 'Active' 'List B' etc.) or is it fine to transfer these choices through the code (show me everyone on List B who's active)?

I managed to set up a stored procedure (hooray) but I'm puzzled by the output. Here it is:

DELIMITER $$
CREATE DEFINER=`DATABASE`@`%` PROCEDURE `PROCEDURENAME`(IN `ListName` TEXT, `Status` TEXT)
BEGIN
 SELECT * 
 FROM TABLE
 WHERE STATUS = STATUS AND ListName = ListName;
 END$$
DELIMITER ;

Select allOpen in new window

The output is the entire database - even though I'm prompted to choose Status and List type. What am I doing wrong?

You're not forgotten. I have three kids making programming a bit of a challenge. I'll update once I try your solution.

Story of my life. Seems like I'm always put on the back burner. I come in second to everyone else. :-)

Hi Doug,

Miracles do happen (it IS that time of year). Based on your suggestions and a bit of tweaking I now have a procedure that reads the needed mysql data from the database.

Here's the working code for other peoples reference:

DELIMITER $$
CREATE DEFINER=`DATABASE`@`%` PROCEDURE `MyFirstSQLProcedure`(IN `ListName` CHAR(20), IN `StatusList` CHAR(20))
    READS SQL DATA
BEGIN
  SELECT Column0, Column1, Column3, Column4, Column5, Column6, Column7 FROM Ventelister
  WHERE Column1 = ListName AND Column7 = StatusList;
END$$
DELIMITER ;

Select allOpen in new window

I think I'll mark it as solved, I've found a few pages talking about how to call procedures from classic asp. I'll try those and if I fail, I'll create a new questions  in here.

Happy new year!

/Michael