Solved

How to use prepared statements on php with mssql library

Posted on 2013-11-13
7
1,473 Views
Last Modified: 2013-11-13
Hi.

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.
0
Comment
Question by:gplana
  • 3
  • 3
7 Comments
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 39644842
Yes, it's possible, but not recommended.  Please see the warning here:
http://php.net/manual/en/ref.pdo-dblib.php

Examples of how to use PDO (albeit with MySQL, not MSSQL) are available here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
LVL 11

Expert Comment

by:Murfur
ID: 39644977
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);
0
 
LVL 15

Author Comment

by:gplana
ID: 39645402
Ok, then I think I will use mssql_query("my query here") just escaping the ' character replacing it by ''. Should this be ok?
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 11

Expert Comment

by:Murfur
ID: 39645566
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 . "'";
or
$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'] ) );
0
 
LVL 15

Author Comment

by:gplana
ID: 39645589
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...
0
 
LVL 11

Accepted Solution

by:
Murfur earned 300 total points
ID: 39645902
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.
0
 
LVL 15

Author Closing Comment

by:gplana
ID: 39645936
Thank you both. Now it's very clear. Regards!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question