[clug] MySQL prepared statements issue

[Tim Murphy]

For those of you who are interested, I did get this working quite well.
Although there is a bit of overhead using prepared statements for single
queries, I believe this is well outweighed by the extra security it offers.

In a nutshell, it works like this:

	1) open a database connection
	2) run the PREPARE statement
	3) SET the values being inserted into variables (if any)
	4) EXECUTE the query with the variables
	5) extract the data
	6) DEALLOCATE the prepared statement
	7) close the database connection

In larger applications this may not be a viable solution, but for smaller or
security critical applications I believe this is an excellent way of doing
database interaction.

I have tested this out on ASP and PHP and have had no issues.

Tim


-----Original Message-----
From: Michael Cohen [mailto:scudette at gmail.com] 
Sent: Thursday, 14 February 2008 4:26 PM
To: Tim Murphy
Subject: Re: [clug] MySQL prepared statements issue

Tim,
Thats your problem then....

ASP and PHP would use a pool of connections which any particular script will
take a handle from. Depending on the phase of the moon you may or may not
get the same handle which has a prepared statement in it when you connect.

I would not bother using prepared statements in this setup - the overhead in
a local mysql connection is just not worth it.

Michael.

On Feb 14, 2008 1:53 PM, Tim Murphy <tim at murphy.org> wrote:
> > Are you using the mysql command line tool or are you going through 
> > another
> layer?  If the layer uses the one database
> > connection for all it's clients then they will all share the same scope.
>
> It's all being run from an ASP script.  Basically it connects to the 
> DB, runs the PREPARE statement, disconnects.  Connects again, stores 
> all the variables, disconnects.  Connects, runs the EXECUTE statement,
disconnects.
>
> This sporadic connecting and disconnecting happens because all DB 
> activity is run through a function: connect, run query, gather data,
disconnect.
>
> > If I'm right you should be able to work around the problem by not
> deallocating the prepared statement.  MySQL will
> > automatically deallocate it when the session is closed.
>
> I removed the DEALLOCATE query from the system but it didn't make much 
> difference.  I did notice, though, that the prepared_stmt_count 
> hovered around the 2 - 5 mark, as it did with the DEALLOCATE left in.
>
>
> --
> linux mailing list
> linux at lists.samba.org
> https://lists.samba.org/mailman/listinfo/linux
>