Error with PHP parameters in SQL function call

Question

I have two PHP variables in a class that are integers ($id and $descCode).

I'm trying to get these into my SQL function call as characters (the database is looking for these to be CHAR 2 and CHAR 10 respectively).

For some reason, this is triggering an error:

Use of parameter marker or NULL not valid

What exactly am I doing wrong here?

$results = array();
$results = $db->select("SELECT newCodeTest(:id,:desc) as newCode FROM testTable",
    [
        'id' => (string)$id,
        'desc' => (string)$descCode
    ]
);

What is `newCodeTest()` ? Where did you get this markup? Never seen anything like it. — GrumpyCrouton, Jun 18 '19 at 20:37
It's a SQL function that I'm using select to call, that's how we have it set up — Geoff_S, Jun 18 '19 at 20:45
What does the function do? And what is the SELECT statement intended to do in general? — showdev, Jun 18 '19 at 20:45
I think I actually fixed it with cast and the specific number — Geoff_S, Jun 18 '19 at 22:23

score 0 · Accepted Answer · answered Jun 18 '19 at 20:34

0

You can not use PDO in this way.

Look at this (possible duplicate): Can PHP PDO Statements accept the table or column name as parameter?.

Set those values as basic string

$id = $pdo->quote($id);
$desc = $pdo->quote($desc);
"SELECT newCodeTest({$id},{$desc}) as newCode FROM testTable"

Info about quoting:

https://www.php.net/manual/en/pdo.quote.php

Interesting info about performance

https://www.php.net/manual/en/pdo.quote.php#122967

answered Jun 18 '19 at 20:34

Radim Kleinpeter

108
2
11

I don't thing the OP is using parameters to select table or column names. Wouldn't the query just select a string literal, or actually two string literals processed by the `newCodeTest()` function? – showdev Jun 18 '19 at 20:38
I suppose newCodeTest(param1, param2) is his own mysql function with 2 required params. Therefore I do not understand downvote. – Radim Kleinpeter Jun 18 '19 at 20:44
I didn't downvote, but it doesn't seem that table or column names are relevant here. – showdev Jun 18 '19 at 20:53
1

I'm assuming the downvote (not mine either) is because you are taking a wild shot at answering a question that you don't have enough details to answer. – GrumpyCrouton Jun 18 '19 at 20:56
There is problem with markers (:id, :desc) which are not allowed in this way. They can be used for example in where conditions, as bounds for insertin or updating data but not as params in own function. – Radim Kleinpeter Jun 18 '19 at 21:13
Even if OP wanted to bind column names, escaping them is not the best solution either. Either white list them or make them constant in the code. – Dharman Jun 18 '19 at 22:07
I actually fixed this by using cast but keeping the markers the same way. @RadimKleinpeter I didn't downvote you but I went ahead and up voted to remove the negative vote – Geoff_S Jun 18 '19 at 22:28

Error with PHP parameters in SQL function call

1 Answers1