Query works in SQL-server but not with php-input

Question

I'm writing a web application in php (which I have a decent knowledge of) and have been given queries to stored procedures in a SQL Server database. This is how I have used one of them:

     function exampleFunction ($productId, $serialNbr, $companyName1, $companyName2, $companyName3) {

    return ' exec dbo.getStuffFromDatabase @product_id = ' . $productId . ', 
                                            @serial_id = ' . $serialNbr . ',
                                        @is_company_name1 = ' . $companyName1 . ', 
                                            @is_company_name2 = ' . $companyName2 . ',

@is_company_name3 = ' . $companyName3;
    }

I have never worked with database programming before, and possibly I am making some simple misstake, but I can't get this to work. I was wondering if there is something I have missed with the $companyName-variables - as far as I can tell they are like booleans (bit:s) that can take the value 0 and 1. I have tried using true/false, integers and strings for these values, none worked. When I execute this query in SQL Server Manager it works fine.

Thanks!

Edit:

The script in Server Management Studio:

declare @product_id varchar(16)
declare @serial_id int
declare @is_company_name1 bit
declare @is_company_name2 bit
declare @is_company_name2 bit

set @product_id = 'all'
set @serial_id = 9999999
set @is_company_name1 = 0
set @is_company_name2 = 0
set @is_company_name2 = 1


exec [dbo].[getStuffFromDatabase] 
@product_id = @product_id,
@serial_id = @serial_id,
@is_company_name1 = @is_company_name1,
@is_company_name2 = @is_company_name2,
@is_company_name3 = @is_company_name3

Can you post an example of the same script as you have it for executing in management studio? (I suspect that you need to get some additional quotation marks into this string). — Damien_The_Unbeliever, Jan 03 '13 at 13:43
It might just be a cut/paste error, but are you missing an '@' in the first argument (product_id)? You don't say what 'doesn't work' means - does this throw an error? — DeanGC, Jan 03 '13 at 14:08
Thanks, that was just me slipping on the keyboard. The only error I can get is that the database returns "false" instead of returning a table, as it is supposed to. — tove, Jan 03 '13 at 14:13
[Obligatory comment warning about SQL injection.](http://en.wikipedia.org/wiki/Sql_injection) — Jon Seigel, Jan 03 '13 at 14:39

score 2 · Accepted Answer · answered Jan 03 '13 at 14:27

You need some quotes around product_id:

return ' exec dbo.getStuffFromDatabase @product_id = \'' . $productId . '\', 
                                        @serial_id = ' . $serialNbr . ',
                                    @is_company_name1 = ' . $companyName1 . ', 
                                        @is_company_name2 = ' . $companyName2 . ',

@is_company_name3 = ' . $companyName3;

However, you might want to see if there are facilities to use parameters rather than building your SQL as a single string. That would go a long way towards helping you avoid the possibility of SQL injection.

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

You need to create a connection from PHP to your SQL Server, preferably using the SQLSRV or PDO extentions. (Wich is basically what SQL Server Manager does for you)

$pdo = new PDO(...);
$stmt = $pdo->prepare('exec dbo.getStuff ...');
$res = $stmt->execute(array($productId, $serialNbr, ...));
return $res;

In place of ... you need to provide the arguments required.

See:

Edit: Found a few examples right here on stackoverflow:

Although they also have their problems, they do give a good impression of what is needed to execute stored procedures.

Edit2: another few examples using sqlsrv_*:

edited May 23 '17 at 12:18

Community

1
1

answered Jan 03 '13 at 13:45

Johannes Konst

428
2
8

The connection to the database is previosly set up and works with other queries - it is just this one that is causing trouble. Sorry for not specifying this. This function is just supposed to return the query. – tove Jan 03 '13 at 14:00
Added a few examples. How is your connection set up? What library do you use? – Johannes Konst Jan 03 '13 at 14:09
SQLSRV. I have a function that connects to the server and database and returns $conn, as in: $conn = sqlsrv_connect( $serverName, $connectionInfo); – tove Jan 03 '13 at 14:15
I'm afraid returning only a query won't work in this case. [sqlsrv_query](http://php.net/manual/en/function.sqlsrv-query.php) accepts not only $conn and $query, but also $params which you need when calling procedures – Johannes Konst Jan 03 '13 at 14:27
Yes, I have a connect2database function which establishes the connection and returns the $conn, and another function that reurns a $stmt, as you describe. These are not the probem. I use them with other functions that call stored procedures, as in the example, which all work fine. It is this specific function that doesn't work for me. Thanks for the help, though. – tove Jan 03 '13 at 14:41

Query works in SQL-server but not with php-input

2 Answers2