Solved: What is the syntax for including a variable in a sqlsrv query

Create Account

PHP

Questions

Followers

Top Experts

vmurray

What is the syntax for including a variable in a sqlsrv query

Just starting to write some apps in PHP using sqlsrv PHP ext driver. Plenty of experience writing with mysql_connect, but couldnt get the mssql_connect to work.

Running IIS, PHP 5.3.2, SQL2005 Driver (sqlsrv), SQL Server 2005 on a remote box.

Here's the issue... I can't get a simple query to execute without errors.

See code, but basically, the query is failing when I add "WHERE UserName=$LoggedInUserName". If I do: "WHERE PersonnelID=1", it works. If I leave off the "WHERE..." it works fine. It seems to be failing on a string. What's the deal with passing in arguments like:
"WHERE UserName=?" then running the query passing in var1, var2, etc...

In other words, I'm connecting, but having trouble passing in data to SQL statement. 1: Does anyone use this driver in production? 2: How do you pass in a simple query?

Error:
PHP Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in...[filepath] on line 11.

<?php
	//Get Logged In User
	$LoggedInUser = $_SERVER["AUTH_USER"];
	$LoggedInUserName = substr_replace($LoggedInUser, $LoggedInUserName, 0, 13);
	
	//print ("$LoggedInUser, $LoggedInUserName<br>");
	
	$tsql = "SELECT * FROM Personnel WHERE UserName=$LoggedInUserName";
	$stmt = sqlsrv_query($conn1, $tsql);
	
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
		print ("Logged In As: $row[PersonnelName] [<strong>$LoggedInUser</strong>]");
	}
	
?>

Open in new window

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

hielo🇼🇫

>>sqlsrv_query
???

did you mean mssql functions:
http://us2.php.net/manual/en/book.mssql.php

vmurray

ASKER

I wish I could get mssql working... Tried replacing NTDB...DLL, tried installing MSSQL2005 Client Tools, etc...

http://msdn.microsoft.com/en-us/library/cc296172(SQL.90).aspx

BrandonGalderisi🇺🇸

Strings have to be in quotes.

I don't know how php does string concatenation, but the SQL has to have single quotes around it. And you are leaving yourself open to SQL injection attacks by passing in SQL like that.

$tsql = "SELECT * FROM Personnel WHERE UserName='" & $LoggedInUserName & "'";

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

vmurray

ASKER

Thanks for fast response.

How can it be passed in without opening up to attacks?

I get:
PHP Parse error: syntax error, unexpected T_STRING in
for:
$tsql = "SELECT * FROM Personnel WHERE UserName='"string"'";

I get no error, but blank result for one that should have result for:
$tsql = "SELECT * FROM Personnel WHERE UserName='" & $LoggedInUserName & "'";

Strange thing though. When I just do a print $tsql, I get:

If I do:
$tsql = "SELECT * FROM Personnel WHERE UserName=$LoggedInUserName";
I print $tsql and get:
SELECT * FROM Personnel WHERE UserName=correctstringfromvar

SOLUTION

BrandonGalderisi🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

vmurray

ASKER

Understand the injection concept. I guess my question is how do you get around it... or even before that, how do you eliminate this error:
PHP Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in ...

print("$tsql"); = SELECT * FROM Personnel WHERE UserName='string' which looks right.

Even if I hardcode the string into the SQL SELECT stmt, I don't get any results. If I take out the string, I get no error and in this case all results printed correctly:
ie: SELECT * FROM table [returns all results with this code]

vmurray

ASKER

Understand the link and method. Actually tried it earlier, but it's still throwing the error:
PHP Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in

When using the code below, I get (and no results):
print ("$tsql"); = SELECT * FROM Personnel WHERE UserName=?

Thoroughly confused...

<?php
	//Get Logged In User
	$LoggedInUser = $_SERVER["AUTH_USER"];
	$LoggedInUserName = substr_replace($LoggedInUser, $LoggedInUserName, 0, 13);
	
	//print ("$LoggedInUser, $LoggedInUserName<br>");
	
	$tsql = "SELECT * FROM Personnel WHERE UserName=?";
	print ("$tsql<br>");
	$stmt = sqlsrv_query($conn1, $tsql, array($LoggedInUserName));
	print ("$stmt<br>");
	
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
		print ("Logged In As: $row[PersonnelName] [<strong>$LoggedInUser</strong>]");
	}
	
?>

Open in new window

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

hielo🇼🇫

>>how do you eliminate this error:
PHP Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in ...
It sounds like your query is failing. I don't see you connecting to the db anywhere. Try:

<?php
/*Connect to the local server using Windows Authentication and specify
the AdventureWorks database as the database in use. To connect using
SQL Server Authentication, set values for the "UID" and "PWD"
 attributes in the $connectionInfo parameter.*/
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks", "UID" => "username", "PWD" => "password");
$conn1 = sqlsrv_connect( $serverName, $connectionInfo);
 
if( !$conn1 )
{
     echo "Connection could not be established.\n";
     die( print_r( sqlsrv_errors(), true));
}
 
	//Get Logged In User
	$LoggedInUser = $_SERVER["AUTH_USER"];
	$LoggedInUserName = substr_replace($LoggedInUser, $LoggedInUserName, 0, 13);
	
	//print ("$LoggedInUser, $LoggedInUserName<br>");
	
	$tsql = "SELECT * FROM Personnel WHERE UserName='?'";
	$params = array($LoggedInUserName);
	$stmt = sqlsrv_query($conn1, $tsql);
	$stmt=sqlsrv_query( $conn1, $tsql, $params);
	if( !$stmt )
	{
      	echo "Error in statement execution.\n";
      	die( print_r( sqlsrv_errors(), true));
	}
 
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
		print ("Logged In As: $row['PersonnelName'] [<strong>$LoggedInUser</strong>]");
	}
	
 
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn1);
?>

Open in new window

Ray Paseur🇺🇸

This statement _might_ be applicable... I can't find the man page for sqlsrv_query, but this applies to mssql_query. http://us3.php.net/manual/en/function.mssql-query.php

Returns a MS SQL result resource on success, TRUE if no rows were returned, or FALSE on error.

If that applies, you would test this way...

Sidebar note, probably want to choose between lines 24 and 25 in the example just above.

Best to all, ~Ray

if ($stmt === TRUE) { /* NOTHING FOUND */ }
if ($stmt === FALSE) { /* QUERY FAILED */ }
// ELSE THIS IS A RESOURCE TO FETCH A RESULT SET

Open in new window

vmurray

ASKER

Thanks for all contributing. I'm passing in DB connection in another function. That piece is working since I do get results when taking out the "WHERE..." in the query.

I do think the query is failing due to trying to pass in the string/var.

Here is the result for the successfull connection/query:

SELECT * FROM Personnel
Resource id #8
Logged In As: John Doe [DOMAIN\jdoe]
Logged In As: Joe Blow [DOMAIN\jblow]

<?php
	//Get Logged In User
	$LoggedInUser = $_SERVER["AUTH_USER"];
	//Strip Domain from LoggedInUserName
	$LoggedInUserName = substr_replace($LoggedInUser, $LoggedInUserName, 0, 13);
	
	//print ("$LoggedInUser, $LoggedInUserName<br>");
	
	//$tsql = "SELECT * FROM Personnel WHERE UserName=?";
	$tsql = "SELECT * FROM Personnel";
	print ("$tsql<br>");
	//$stmt = sqlsrv_query($conn1, $tsql, array($LoggedInUserName));
	$stmt = sqlsrv_query($conn1, $tsql);
	print ("$stmt<br>");
	
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
		print ("Logged In As: $row[PersonnelName] [<strong>$LoggedInUser</strong>]");
	}
	
?>

Open in new window

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

vmurray

ASKER

Here is the result when trying to pass in the query string:

SELECT * FROM Personnel WHERE UserName=?
{blank}
PHP Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\filepath\filename.php on line 16

<?php
	//Get Logged In User
	$LoggedInUser = $_SERVER["AUTH_USER"];
	//Strip Domain from LoggedInUserName
	$LoggedInUserName = substr_replace($LoggedInUser, $LoggedInUserName, 0, 13);
	
	//print ("$LoggedInUser, $LoggedInUserName<br>");
	
	$tsql = "SELECT * FROM Personnel WHERE UserName=?";
	//$tsql = "SELECT * FROM Personnel";
	print ("$tsql<br>");
	$stmt = sqlsrv_query($conn1, $tsql, array($LoggedInUserName));
	//$stmt = sqlsrv_query($conn1, $tsql);
	print ("$stmt<br>");
	
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
		print ("Logged In As: $row[PersonnelName] [<strong>$LoggedInUser</strong>]");
	}
	
?>

Open in new window

Ray Paseur🇺🇸

see code example below:

// CHANGE TO THIS WITH QUOTE MARKS
        $tsql = "SELECT * FROM Personnel WHERE UserName='?'";

Open in new window

vmurray

ASKER

Same error. Just recieve
SELECT * FROM Personnel WHERE UserName='?'

on screen print.

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

vmurray

ASKER

Do you have to match or define the variables when they are text/strings? When I query on ID(int), it succeeds and I receive a correct result with no errors. PersonnelName is a "text" type in SQL. It fails on all text queries in the DB.

When I run:
$tsql = "SELECT * FROM Personnel WHERE PersonnelID=1";

I get (which is correct)
SELECT * FROM Personnel WHERE PersonnelID=1
Resource id #8
Logged In As: John Doe [DOMAIN\jdoe]

Ray Paseur🇺🇸

You have to quote the text strings.

SOLUTION

Ray Paseur🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

vmurray

ASKER

This is MS SQL using sqlsrv driver. Is there an error() function to echo like MYSQL?

I agree that it is related to passing strings, but even stripping out the "complexity" of passing variables, it fails passing a static string in quotes:

Print:
SELECT * FROM Personnel WHERE UserName='jdoe'
PHP Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\filepath\filename.php on line 16

<?php
	//Get Logged In User
	$LoggedInUser = $_SERVER["AUTH_USER"];
	//Strip Domain from LoggedInUserName
	$LoggedInUserName = substr_replace($LoggedInUser, $LoggedInUserName, 0, 13);
	
	//print ("$LoggedInUser, $LoggedInUserName<br>");
	
	//$tsql = "SELECT * FROM Personnel WHERE UserName=?";
	$tsql = "SELECT * FROM Personnel WHERE UserName='jdoe'";
	print ("$tsql<br>");
	//$stmt = sqlsrv_query($conn1, $tsql, array($LoggedInUserName));
	$stmt = sqlsrv_query($conn1, $tsql);
	print ("$stmt<br>");
	
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
		print ("Logged In As: $row[PersonnelName] [<strong>$LoggedInUser</strong>]");
	}
	
?>

Open in new window

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

hielo🇼🇫

>> Is there an error() function to echo like MYSQL
Yes, I posted above. Read through the code:
sqlsrv_errors()

SOLUTION

hielo🇼🇫

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

vmurray

ASKER

Thanks. Here's the result:

SELECT * FROM Personnel WHERE UserName=jdoe

Error in statement execution. Array ( [0] => Array ( [0] => 42S22 [SQLSTATE] => 42S22 [1] => 207 [code] => 207 [2] => [Microsoft][SQL Native Client][SQL Server]Invalid column name 'jdoe'. [message] => [Microsoft][SQL Native Client][SQL Server]Invalid column name 'jdoe'. ) )

Ray Paseur🇺🇸

Try quoting jdoe so SQL thinks it is data instead of a column name. HTH, ~Ray

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

hielo🇼🇫

try:

<?php
	//Get Logged In User
	$LoggedInUser = $_SERVER["AUTH_USER"];
	//Strip Domain from LoggedInUserName
	$LoggedInUserName = substr_replace($LoggedInUser, $LoggedInUserName, 0, 13);
	
	$tsql = "SELECT * FROM Personnel WHERE UserName=?";
	$params=array('jdoe');
	$stmt = sqlsrv_query($conn1, $tsql,$params);
	if( FALSE === $stmt)
	{
		echo "Error in statement execution:<br>\n$tsql<br>\n";
      	die( print_r( sqlsrv_errors(), true));
 
	}
	print ("$stmt<br>");
	
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
		print ("Logged In As: $row['PersonnelName'] [<strong>$LoggedInUser</strong>]");
	}
	
?>

Open in new window

ASKER CERTIFIED SOLUTION

vmurray

ASKER

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Ray Paseur🇺🇸

Praises be! Glad you got over this one. ~Ray

hielo🇼🇫

>>Assuming text values can't be passed into a SQL query string...
Doubtful. Try:

<?php
	//Get Logged In User
	$LoggedInUser = $_SERVER["AUTH_USER"];
	//Strip Domain from LoggedInUserName
	$LoggedInUserName = substr_replace($LoggedInUser, $LoggedInUserName, 0, 13);
/*
 
	$tsql = "SELECT * FROM Personnel WHERE id=? OR UserName=?";
	$params=array( 
			array('jdoe',SQLSRV_PARAM_IN,SQLSRV_PHPTYPE_INTEGER,SQLSRV_SQLTYPE_BIGINT),
			array('jdoe',SQLSRV_PARAM_IN,SQLSRV_PHPTYPE_STRING,SQLSRV_SQLTYPE_TEXT) 
		);
*/
	$tsql = "SELECT * FROM Personnel WHERE UserName=?";
	$params=array( 
			array('jdoe',SQLSRV_PARAM_IN,SQLSRV_PHPTYPE_STRING,SQLSRV_SQLTYPE_TEXT) 
		);
 
	$stmt = sqlsrv_query($conn1, $tsql,$params);
	if( FALSE === $stmt)
	{
		echo "Error in statement execution:<br>\n$tsql<br>\n";
      	die( print_r( sqlsrv_errors(), true));
 
	}
	print ("$stmt<br>");
	
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
		print ("Logged In As: $row['PersonnelName'] [<strong>$LoggedInUser</strong>]");
	}
	
?>

Open in new window

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

hielo🇼🇫

the last post was meant to be tried with a "text" column of course (as opposed to varchar)

PHP

Questions

Followers

Top Experts

PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.