Parameterized queries

Anon

Does PHP support parameterized queries? I haven't seen any documentation on this....

toma42

PHP uses string concatonation to form dynamic queries. Hope that you understand this answer....I've never seen a true paramertized query in any SQL. As for views, etc, just supply an extra where statement to filer.

Anon

ASP and VB support the Command class, and Java supports the PreparedStatement class. Each instance of these is a parameterized object that allows dissection of stored procedures and specification of data fields as parameters.

I haven't seen anything in PHP that's similar, but I've only been using it for a week. PHP seems to me to be less concerned with OOP and more with getting things done fastest.

Anon

Here's a sample of what i was talking about (written in ASP). This script executes the named procedure, dynamically generates a parameter list, searches the specified parameter list (i.e. POST aka Request object) for the data, casts it appropriately, and executes the procedure.

I wrote it about a year ago, and haven't had to write an ASP sql string since.

'executeProcedure
'****************************************************************************
'This function executes the named stored procedure
'All the required parameters are retrieved from the specified collection
'The function returns an array (if a recordset is generated by the query)
'Or true or false if there is no recordset generated by the query
'****************************************************************************
Function executeProcedure(procedureName, parameters)
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Dim prm
Set prm = Server.CreateObject("ADODB.Parameter")
Dim rst
Set rst = Server.CreateObject("ADODB.Recordset")
Dim key

On Error Resume Next
Err.Clear()		
cmd.ActiveConnection = cnn
cmd.CommandType = 4
cmd.CommandText = procedureName
cmd.Parameters.Refresh
For Each prm In cmd.Parameters
	key = Replace(prm.Name, "@", "")
	Select Case prm.Type
	Case 0
		'empty
	Case 2
		'smallint
		prm.Value = CInt(parameters(key))
	Case 3
		'int
		prm.Value = CLng(parameters(key))
	Case 4
		'single
		prm.Value = CSng(parameters(key))
	Case 5
		'double
		prm.Value = CDbl(parameters(key))
	Case 6
		prm.Value = CCur(parameters(key))
	Case 7, 133, 134
		'date
		prm.Value = CDate(parameters(key))
	Case 11
		'boolean
		prm.Value = CBool(parameters(key))
	Case 12
		'variant
		prm.Value = CVar(parameters(key))
	Case 14
		'decimal
	Case 129, 200
		'char, varchar
		prm.Value = Left(CStr(parameters(key)), prm.Size)
	Case Else
		prm.Value = parameters(key)
	End Select
Next
Set rst = cmd.Execute
Set executeProcedure = rst

'If Err.number <> 0 Then executeProcedure = Err.Description
On Error Goto 0

'Cannot close the connection
'cnn.Close()
'Set cnn = Nothing

Set cmd = Nothing
Set prm = Nothing

End Function

cookd

Parameterized queries and Stored Procedure queries are supported on a by-driver basis. For example, the PHP Oracle and ODBC drivers support parameterized queries and SP calls. MySQL, PostgreSQL, and Sybase drivers do not. A few extra procedures have recently been hacked on to the MSSQL/Sybase drivers (is it specific to MSSQL or does it work with Sybase too? I dunno) that let you use parameters to stored procedures.

If you ask me, this is a REAL PROBLEM.

Parameterized queries are so much better than escaping text -- more efficient on both the server and the client side, and much less likely to end up in confusion (now I striplashed the magicquotes, but they were already addslashed before, so I stripslash again, then nl2br, then...). There is the issue of matching the question-mark or parameter up with the PHP variable it goes with, but in my opinion this is much easier than the problems with escaping.

For those who aren't familiar with parameterizing queries, you do something like this:

prepare(
"INSERT INTO MyTable Set A=?, B=?, C=?",
$aVal, $bVal, $cVal
);

The variables can have any contents, and the most efficient method of sending the contents is chosen by the driver. Usually, this is via sending a length field and then the data, which means no escaping is ever done. With some APIs, you can call a stored procedure and pass one of the parameters by reference, which allows it to become an OUTPUT parameter.

I really like PHP. In my opinion, its greatest weakness is in its wacky database support. It supports a lot of different databases, but all in different ways and at different levels. Without a good way to escape binary data (at all!), I currently consider PHP's SYBASE driver broken... Oh well.