note 95968 deleted from function.mssql-query by danbrown
| From: | danbrown@php.net | Date: | Mon, 01 Feb 2010 01:57:55 +0000 |
| Subject: | note 95968 deleted from function.mssql-query by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-165540@lists.php.net to get a copy of this message | ||
Note Submitter: TheronP
----
To avoid SQL Injection, if you want to have multiple named parameters without writing stored
procedures you can use built in stored procedure sp_executesql and mssql_query:
<?php
//Returns records with Id#46 or first name starting with 'Mary'
$server = '127.0.0.1';
$link = mssql_connect($server, 'sql_user', 'sql_user_pass');
//Select DB
$dbn = 'dbName';
mssql_select_db($dbn);
//input variables
$name='Mary%';
$id=46;
//necesssary for stored procedure
$params="@name varchar(50),@id int";
$paramslist="@name='$name',@id='$id'";
$sql = "select FirstName,LastName,EmpId from employees where FirstName like @name or EmpId =
@id " ;
$dbsql = "EXEC sp_executesql
N'$sql',
N'$params',
$paramslist";
//important to have the "N'" !
// didn't have a problem with using varchar for all my inputs on updates no matter what field
type in db. Seems MSSQL casts the input variable to the field type.
$result=mssql_query($dbsql,$link);
?>
for more info on sp_executesql go here:
http://msdn.microsoft.com/en-us/library/ms188001.aspx
works on MSSQL 2000,2005,2008