Search parameters SQLSRV_QUERY for WHERE IN syntax

Question

I wonder if there is a way to pass some values into the parameters option on the sqlsrv_query function. I tried a few things but could not get it to work.

This query is what I want to be executed:

SELECT id, name, etc
FROM sqlTable
WHERE id IN ('1', '2', '100', '314')

I want to pass the WHERE IN values using the params option, like this:

$q = "SELECT id FROM sqlTable WHERE id IN ?";
$p = array(array('1', '2', '100', '314'));
sqlsrv_query($connection, $q, $p);

Right now I'm passing the values directly into the query string, but for obvious security reasons I want to pass them as parameters into the function. Anyone any idea on how to achieve this?

score 0 · Answer 1 · answered Mar 05 '16 at 01:31

0

Consider PDO binded parameters which you can pass a defined array in execute(). However, you would need to prepare the statement, knowing number of IN() clause items in advance.

try {
    $dbh = new PDO("sqlsrv:server=$server;database=$database",$username,$password);

    $sql = "SELECT * FROM sqlTable WHERE id IN (:first, :second, :third, :fourth)";    
    $STH = $dbh->prepare($sql);    
    $nums = array('1', '2', '100', '314');
    $STH->execute($nums);

}
catch(PDOException $e) {  
    echo $e->getMessage()."\n";
}

answered Mar 05 '16 at 01:31

Parfait

104,375
17
94
125

That is also possible with the Microsoft SQL Server Driver. I was actually hoping on a cleaner solution where you could simply state `IN(:first)` and pass an array or string with all the clause values in it. – Fin Mar 05 '16 at 01:40
Parameters need placeholders. You have to prepare in advance its structure. And this would arguably be the *cleaner* version. How can one bind values to an indeterminate number of elements? – Parfait Mar 05 '16 at 02:07

score 0 · Accepted Answer · answered Mar 06 '16 at 17:00

So I have figured out this issue on the sql side. Now I pass a comma separated string with the ids to the query using the params in the sqlsrv_query() function. The query sets the string in a temporarily variable. Using a splitting function every id is stored in a temporarily table. As last I JOIN the temporarily table with the table from witch I want to get the results.

Splitting function in SQL:

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

PHP code and SQL query:

$q = "
DECLARE @inStr varchar(max)
SET @inStr = ?

DECLARE @tmpTable table (tmpID varchar(200))

INSERT @tmptable (tmpID)
SELECT * FROM dbo.splitstring(@inStr)

SELECT id, name, etc
FROM sqlTable
JOIN @tmpTable ON id = tmpID";
$p = array('1,2,100,314');
sqlsrv_query($connection, $q, $p);

Why not just create a stored procedure altogether and execute it with string parameter in PHP? The enduring debate in SQL: [parameterized query vs stored procedure](https://www.google.com/#q=parameterized+queries+vs+stored+procedure). — Parfait, Mar 11 '16 at 04:20

Search parameters SQLSRV_QUERY for WHERE IN syntax

2 Answers2