Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to use prepared statements on php with mssql library

Posted on 2013-11-13
7
Medium Priority
?
1,656 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1200 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

772 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