php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #39143 prepared statement with input parameters and db function fails
Submitted: 2006-10-13 02:43 UTC Modified: 2006-10-13 06:41 UTC
From: aspen dot olmsted at alliance dot biz Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5CVS-2006-10-13 (snap) OS: Windows XP SP2
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: aspen dot olmsted at alliance dot biz
New email:
PHP Version: OS:

 

 [2006-10-13 02:43 UTC] aspen dot olmsted at alliance dot biz
Description:
------------
If you try to insert a record using MSSQL, ODBC, PDO and windows with a replacement parameter that is a db function it will fail.  The same insert can be written without replacement parameters using both exec and prepare.

Reproduce code:
---------------
SQL Table:
CREATE TABLE [pdotest] (
	[Code] [int] NOT NULL ,
	[Description] [varchar] (100) 
) ON [PRIMARY]
GO

SQL Function:
CREATE FUNCTION dbo.pdo_GetCode (@inCode int)  
RETURNS int AS  
BEGIN
return @incode
END

$a[Code] = 'dbo.pdo_getcode(26050)';
$a[Description] = 'test';

$stmt = $dbh->prepare("insert into pdotest(Code,Description) VALUES (:Code,:Description)");
$x = $stmt->execute($a);
$stmt = null;
    
$stmt = $dbh->prepare("insert into pdotest(Code,Description) VALUES (dbo.pdo_getcode(26050),'test')");
$x = $stmt->execute();
$stmt = null;

$x = $dbh->exec("insert into pdotest(Code,Description) VALUES (dbo.pdo_getcode(26050),'test')");

Expected result:
----------------
With the above code all three methods should insert a record

Actual result:
--------------
The first method fails with the replacement parameters and the other two pass.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-10-13 06:41 UTC] thetaphi@php.net
This cannot work.
A replacement parameter is not to replace parts of the SQL code, it is for setting data values before execution. Databases that support it will compile the code before execution which then will generate a speedup when the same statement is called more often with different data values.
So: A replacement parameter must resove to data not a sql fragment.
In your example the following SQL is executed after replacement:

insert into pdotest(Code,Description) VALUES
('dbo.pdo_getcode(26050)','test')

This will fail. A correct statement would be with a data parameter, e.g.:

$a[Code] = 26050;
$a[Description] = 'test';

$stmt = $dbh->prepare("insert into pdotest(Code,Description) VALUES
(dbo.pdo_getcode(:Code),:Description)");
$x = $stmt->execute($a);
$stmt = null;

 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Oct 08 22:01:27 2024 UTC