Solved

How to use prepared statements on php with mssql library

Posted on 2013-11-13
7
1,420 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 108

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now