How to use prepared statements on php with mssql library


I have a php web page which connects to SQL-Server. PHP version is 5.2 and is installed over a LAMPP installation on a suse linux.

I'm using mssql_connect to connect to SQL-Server (and then mssql_query, mssql_result, etc...). However I would like to use prepared statements. Is this possible? How can I do this?

Thank you.
LVL 15
Who is Participating?
MurfurConnect With a Mentor Full Stack DeveloperCommented:
So sorry - I was typing faster than I could think!

Actually, MS SQL uses a single quote to escape as you identify a string by enclosing it in single quotes. But you are absolutely right that there is no in-built function that is the equivalent to mysql_real_escape_string but you can use str_replace to effect the same change to the string:

$this =  str_replace( "'", "''", $_REQUEST['this'] );

Personally, I would probably still use stripslashes and mysql_real_escape_string as they will clean up the string value and give you a guaranteed pattern to match in the str_replace i.e.

$this =  str_replace( "\'", "''", mysql_real_escape_string( stripslashes( $_REQUEST['this'] ) );

FYI when I'm doing my initial dev I will run and echo out the functions individually so that I can watch the string change. once happy with the results of my tests I will then concatenate the functions to make tidy code.
Ray PaseurConnect With a Mentor Commented:
Yes, it's possible, but not recommended.  Please see the warning here:

Examples of how to use PDO (albeit with MySQL, not MSSQL) are available here:
MurfurFull Stack DeveloperCommented:
If you are trying to hide your SQL code then you could just pass your variables to a stored procedure:

mssql_query( 'exec MyStoredProcedure @param1 = '  .  $value1 . ', @param2=' . $value2 . ',', $conn);
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

gplanaAuthor Commented:
Ok, then I think I will use mssql_query("my query here") just escaping the ' character replacing it by ''. Should this be ok?
MurfurFull Stack DeveloperCommented:
Best to use the function mysql_real_escape_string() to escape values before they are put into a query e.g.:

$this =  mysql_real_escape_string( $_REQUEST['this'] );
$that =  mysql_real_escape_string( $_REQUEST['that'] );

$sql = "SELECT something FROM database WHERE this='" . $this . "' AND that='" . $that . "'";
$sql = "EXEC SP_DoesSomething @Param1='" . $this . "', @Param2='" . $that . "'";

mssql_query( $sql, $conn );

I assume you are trying to prevent injection so you might also consider using stripslashes() too as this will clean up any back slashes in the request variables, intended or not

// remove slashes from received value
$this =  stripslashes( $_REQUEST['this'] );
$that =  stripslashes( $_REQUEST['that'] );

// add slashes to escape special characters
$this =  mysql_real_escape_string( $this );
$that =  mysql_real_escape_string( $that );

and you can always concatenate the functions to make for tidier code:

$this =  mysql_real_escape_string( stripslashes( $_REQUEST['this'] ) );
$that =  mysql_real_escape_string( stripslashes( $_REQUEST['that'] ) );
gplanaAuthor Commented:
But I think mysql escapes a quote with \' meanwhile sql-server escapes a quote with ''
So I don't think I should use mysql_real_escape_string with sql-server...
gplanaAuthor Commented:
Thank you both. Now it's very clear. Regards!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.