How do I use a MySQL user-defined function from within PHP?

Question

Spent several hours searching for an answer without success. I've written a user-defined function in MySQL which is passed an identifier which it uses to retrieve various pieces of data, concatenate it into one string and return it. I want to call this function from my PHP page and output the result.

Unsuccessful attempts include:

1. $result = mysql_query("select functionName($id)"); 

2. $sql = "select functionName($id)";
   $result = mysql_query($sql, $link);

3. functionName($id)

Any ideas?

Do you refer to a stored procedure? If so.. you need to call YourSP() — Matt Bannert
– Matt Bannert, Commented Jan 25, 2011 at 16:14
You are on the right track with (1) and (2). What sort of error messages are you receiving? Does the query work just fine when you run it in a MySQL query tool? — Justin Ethier
– Justin Ethier, Commented Jan 25, 2011 at 16:14

siride · Accepted Answer · 2011-01-25 18:34:07Z

1

1 and 2 are close, but $result is not going to contain the result of the function call. Rather, it is going to contain the result cookie from the query. You can use that cookie to get the actual data, with mysql_fetch_row(). The function call just returns a value for the select statement, just the same as "SELECT 42" or "SELECT a FROM MyTable". So to get the result you would use the same mechanism as with any other SQL query that returns results; that is, use the cookie and call mysql_fetch_row(). So your final code will look like this:

$result = mysql_query("select functionName($id)");
$row = mysql_fetch_row($result, $link);
$returnValue = $row[0];

Note that you don't want to be interpolating variables directly into an SQL string (that can be a vector for attacks). I assume, however, that this code is just for example purposes.

answered Jan 25, 2011 at 18:34

siride

212k5 gold badges43 silver badges63 bronze badges

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

4 Comments

Chelle Over a year ago

The value of $id has been passed from the previous search selection page to identify the record the user wants to access. What's the safe way for me to do this?

siride Over a year ago

@Chelle: use mysql_real_escape_string() on the value of $id: $sql = "select functionName(" . mysql_real_escape_string($id, $link) . ")" and then use $sql in mysql_query()

Hammerite Over a year ago

your method for sanitising $id is not adequate; consider for example the behaviour if $id contains 3) FROM Users WHERE 1 = 1 -- (the resulting query is select functionName(3) FROM Users WHERE 1 = 1 -- ); the function is run once for every row in table Users, if it exists).

siride Over a year ago

@Hammerite: good catch. preg_match() is a good next step. It's been a while since I've done webdev, so I missed this vector.

luke_mclachlan · Accepted Answer · 2017-01-26 14:49:21Z

0

I had the same question and found this very useful write up from devx, particulary the part at the bottom about calling MySQL functions:

http://www.devx.com/webdev/Article/42887/0/page/2

With regards to mysqli, my code is now as follows:

$result = mysqli_query($sqlconnection,"SELECT functionName($id)");
$row = mysqli_fetch_row($result);
return $row[0];

works perfectly.

answered Jan 26, 2017 at 14:49

luke_mclachlan

1,0541 gold badge15 silver badges35 bronze badges

Collectives™ on Stack Overflow

How do I use a MySQL user-defined function from within PHP?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related