Solved

How to use prepared statements on php with mssql library

Posted on 2013-11-13
7
1,555 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 110

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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
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

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!

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …

624 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