After some effort getting the MSSQL drivers in PHP, I was ready to hook into the RDBMS and use my nicely developed stored procedures. FreeTDS required minimal setup and configuration and I was able to establish a connection and run simple queries against tables in no-time.

However, when I attempted to execute stored procedures, they would fail. To make troubleshooting harder, the MSSQL functions do not return error messages — at least none that I could access in PHP 5.1. My work around to the proper methodology init(), bind(), and execute() process was to use query() and pass in the procedure and parameters as needed. It’s slightly less elegant but opens the data up to you.

// Establish the connection and run the procedure
$connection = mssql_connect("123.123.123.123","db_login","db_password");
mssql_select_db("db_schemaname",$connection);
 
// Run the procedure
$username = "user"; // sanitized!!!
$password = "secret_password"; // sanitized!!!
$result = mssql_query("CheckLogin '$username', '$password'", $connection);
 
// handle the result as needed

The standard disclaimers apply about validating, escaping and bullet proofing any data passed into Microsoft SQL Server using this method. Seriously, heed these warnings, sanitize any data, or you will find your site vulnerable to SQL Injection attacks which are serious business.

Tags: database, freetds, microsoft, mssql, php, sql server, stored procedures

This entry was posted on Wednesday, October 1st, 2008 at 11:40 am and is filed under mssql, php, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.