Wednesday, February 02, 2011

beware of mysqli_stmt_execute clogging your database connection

There are multiple advantages in using prepared statements with MySQL. Improved performance and escaping of parameters passed to queries, to prevent SQL injection attacks come first to mind.

However, you need to be careful with mysqli_stmt_execute, especially if you run queries that produce result sets. If for some reason (e.g. alternate code path taken due to some error) you don't fetch the content of the result set, and if you don't explicitly close the prepared statement, any new queries sent over the database connection associated with the prepared statement that has results pending will fail.

That is, until you fetch the results with mysqli_stmt_fetch, or close the statement with mysqli_stmt_close, you won't be able to reuse the database connection to issue new queries.

This is intuitive enough, albeit a bit surprising, as it behaves very different from the regular mysqli_query that uses non-prepared statements, which is not subject to such constrains.

1 comment:

Anonymous said...

I am trying to insert a record in four tables but mysql_stmt_execute is not working and number of affected rows returned is -1 every time.