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
– Damien_The_Unbeliever, Commented Jan 3, 2013 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
– DeanGC, Commented Jan 3, 2013 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
– tove, Commented Jan 3, 2013 at 14:13

Damien_The_Unbeliever · Accepted Answer · 2013-01-03 14:27:08Z

2

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.

answered Jan 3, 2013 at 14:27

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:18:46Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered Jan 3, 2013 at 13:45

Johannes Konst

4282 silver badges8 bronze badges

5 Comments

tove Over a year ago

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.

Johannes Konst Over a year ago

Added a few examples. How is your connection set up? What library do you use?

tove Over a year ago

SQLSRV. I have a function that connects to the server and database and returns $conn, as in: $conn = sqlsrv_connect( $serverName, $connectionInfo);

Johannes Konst Over a year ago

I'm afraid returning only a query won't work in this case. sqlsrv_query accepts not only $conn and $query, but also $params which you need when calling procedures

tove Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related