Re: [PHP] Pros/Cons of using mysqli prepared statments
On 5 November 2010 05:21, Tamara Temple tamouse.li...@gmail.com wrote: On Nov 4, 2010, at 6:36 AM, Jay Blanchard wrote: [snip] If you have a query in your PHP code, which you are going to be executing a lot, even if you are using prepared statements, you can go one further by creating a stored procedure. Now the SQL server will only ever need to compile the statement once. No matter how many times it is used. You only need to supply the data which will be type appropriate. [/snip] I second this, using stored procedures has a lot of advantages. If you need to change your SQL you can do it in one spot. It reinforces MVS or modular coding behavior, the SP becomes very re-usable. Security is improved. Performance can be improved. You can put the bulk of the data handling on the database where it really belongs because SP's can perform complex operations complete with control structures. Lastly it simplifies your PHP code. (dangit, i sent this from the wrong address initially) I do know about stored procedures and have used them where appropriate (retrieving the entire contents of a table, one record from a table, etc.). It was the prepared statements that I haven't had experience with. I wasn't away that these were precompiled. That does make them more attractive for heavily executed pulls. On the other hand, the seem to require more intense maintenance than just changing some lines of code in a file if need be. (I assume prepared statements don't share the same efficiency of maintenance that stored procedures do across applications.) Ad hoc queries. These will need to be compiled every single time it is used. If it is in a loop, then every single time the server has to go through the compile process. Prepared statements. These will be compiled every time the prepare statement is called. You can execute the query multiple times, but only the prepare will actually be compiled. Stored procedures. In many regards, these are like prepared statements. Depending upon your DB, you may be able to call a stored procedure directly without the need of building an SQL statement to call the SP. If you have to build a query, then you should build a prepared statement, even if you are only calling it once. The advantage is that passing parameters to stored procedures and prepared statements significantly reduce the potential for SQL injection. But, as mentioned, one of queries don't need to be SP. The main advantage I've found is I can get significant optimization on my SQL server if the server knows about it's workload before it is used. I can optimize my indexes for my actual usage. For more complex select statements, then server side views are another optimization you can do. So, not a server side stored procedure, not a client side prepared statement, but a server side prepared statement. Again, client side you can use a prepared statement to get multiple hits on the view (if that's how you are going to run). Basically, ad hoc queries result in more complex client side code. You have to do all the SQL injection protection yourself (or use a Poka-Yoke - STRONGLY recommend). If you can do all the DB related work on the DB, your client code is simpler. The DB work exists in 1 place. Everyone will know where it is. Using the right tool for the job, AND getting the balance right is what being an experienced developer is all about. Start with what you can understand. Ask questions. Learn. Refactor. Improve. Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Pros/Cons of using mysqli prepared statments
I'm wondering what the advantages/disadvantage of using prepared statements with mysqli are. I'm used to using the mysqli::query and mysqli::fetch_assoc functions to deal with retrieving data and bulding my sql statement in php code. Tamara Temple -- aka tamouse__ tam...@tamaratemple.com May you never see a stranger's face in the mirror. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Pros/Cons of using mysqli prepared statments
Tamara, In one of the earlier threads, it was mentioned mysqli APIs are more secure, faster, and actually maintained. Also, if you use some of the mysql_xxx(), you actually get a warning saying that it will be or it is being deprecated and paves the way for you to approach the same thing with mysqli_xxx(). The seniors perhaps can give you more facts which they have seen and experienced. Regards, Shreyas On Thu, Nov 4, 2010 at 1:18 PM, Tamara Temple tam...@tamaratemple.comwrote: I'm wondering what the advantages/disadvantage of using prepared statements with mysqli are. I'm used to using the mysqli::query and mysqli::fetch_assoc functions to deal with retrieving data and bulding my sql statement in php code. Tamara Temple -- aka tamouse__ tam...@tamaratemple.com May you never see a stranger's face in the mirror. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Regards, Shreyas Agasthya
Re: [PHP] Pros/Cons of using mysqli prepared statments
On 4 November 2010 08:19, Shreyas Agasthya shreya...@gmail.com wrote: Tamara, In one of the earlier threads, it was mentioned mysqli APIs are more secure, faster, and actually maintained. Also, if you use some of the mysql_xxx(), you actually get a warning saying that it will be or it is being deprecated and paves the way for you to approach the same thing with mysqli_xxx(). The seniors perhaps can give you more facts which they have seen and experienced. Regards, Shreyas On Thu, Nov 4, 2010 at 1:18 PM, Tamara Temple tam...@tamaratemple.comwrote: I'm wondering what the advantages/disadvantage of using prepared statements with mysqli are. I'm used to using the mysqli::query and mysqli::fetch_assoc functions to deal with retrieving data and bulding my sql statement in php code. Tamara Temple -- aka tamouse__ tam...@tamaratemple.com May you never see a stranger's face in the mirror. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Regards, Shreyas Agasthya I don't use mysql, I use MS SQL, but the principles are pretty much the same. A prepared statement allows the SQL server to work out what needs to be done prior to actually doing it. The server essentially compiles the SQL statement and provides places for you to put in type appropriate values. A prepared statement can be executed repeatedly with different data, without the need of the SQL server having to recompile the query. Due to the SQL server knowing the column types you are using when you are going to supply data to the query, the data types are managed for you. Hmm, I've not explained that very well. Basically, a prepared statement is a lot harder to get SQL injection code working. Normally SQL will handle a string as a string and not as part of the SQL statement. So a password of ' or 1 will be treated as that and NOT as an or statement on a where clause (assuming a simple SQL injection). http://en.wikipedia.org/wiki/Prepared_statements#Parameterized_statements covers this sort of stuff. Now, taking this one stage further. If you have a query in your PHP code, which you are going to be executing a lot, even if you are using prepared statements, you can go one further by creating a stored procedure. Now the SQL server will only ever need to compile the statement once. No matter how many times it is used. You only need to supply the data which will be type appropriate. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Pros/Cons of using mysqli prepared statments
On 4 November 2010 10:42, Richard Quadling rquadl...@gmail.com wrote: On 4 November 2010 08:19, Shreyas Agasthya shreya...@gmail.com wrote: Tamara, In one of the earlier threads, it was mentioned mysqli APIs are more secure, faster, and actually maintained. Also, if you use some of the mysql_xxx(), you actually get a warning saying that it will be or it is being deprecated and paves the way for you to approach the same thing with mysqli_xxx(). The seniors perhaps can give you more facts which they have seen and experienced. Regards, Shreyas On Thu, Nov 4, 2010 at 1:18 PM, Tamara Temple tam...@tamaratemple.comwrote: I'm wondering what the advantages/disadvantage of using prepared statements with mysqli are. I'm used to using the mysqli::query and mysqli::fetch_assoc functions to deal with retrieving data and bulding my sql statement in php code. Tamara Temple -- aka tamouse__ tam...@tamaratemple.com May you never see a stranger's face in the mirror. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Regards, Shreyas Agasthya I don't use mysql, I use MS SQL, but the principles are pretty much the same. A prepared statement allows the SQL server to work out what needs to be done prior to actually doing it. The server essentially compiles the SQL statement and provides places for you to put in type appropriate values. A prepared statement can be executed repeatedly with different data, without the need of the SQL server having to recompile the query. Due to the SQL server knowing the column types you are using when you are going to supply data to the query, the data types are managed for you. Hmm, I've not explained that very well. Basically, a prepared statement is a lot harder to get SQL injection code working. Normally SQL will handle a string as a string and not as part of the SQL statement. So a password of ' or 1 will be treated as that and NOT as an or statement on a where clause (assuming a simple SQL injection). http://en.wikipedia.org/wiki/Prepared_statements#Parameterized_statements covers this sort of stuff. Now, taking this one stage further. If you have a query in your PHP code, which you are going to be executing a lot, even if you are using prepared statements, you can go one further by creating a stored procedure. Now the SQL server will only ever need to compile the statement once. No matter how many times it is used. You only need to supply the data which will be type appropriate. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY And why this sort of thing should be taught at school ... http://xkcd.com/327/ -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Pros/Cons of using mysqli prepared statments
[snip] If you have a query in your PHP code, which you are going to be executing a lot, even if you are using prepared statements, you can go one further by creating a stored procedure. Now the SQL server will only ever need to compile the statement once. No matter how many times it is used. You only need to supply the data which will be type appropriate. [/snip] I second this, using stored procedures has a lot of advantages. If you need to change your SQL you can do it in one spot. It reinforces MVS or modular coding behavior, the SP becomes very re-usable. Security is improved. Performance can be improved. You can put the bulk of the data handling on the database where it really belongs because SP's can perform complex operations complete with control structures. Lastly it simplifies your PHP code. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Pros/Cons of using mysqli prepared statments
Jay Blanchard jblanch...@pocket.com didst scribe: using stored procedures has a lot of advantages. If you need to change your SQL you can do it in one spot. It reinforces MVS or modular coding behavior, the SP becomes very re-usable. Security is improved. Performance can be improved. You can put the bulk of the data handling on the database where it really belongs because SP's can perform complex operations complete with control structures. Lastly it simplifies your PHP code. Just don't go too far. Years and years ago, I worked on a project where there were about 100 stored procedures, many of which were 200-300 lines long, many of which called other stored procedures which called other stored procedures. These procedures were frequently modified. Attempting to debug this can of worms full of Pandora's boxes was like pulling hen's teeth. The initial idea was for the app to do almost nothing but call stored procedures and display results; this caused a number of problems which were ignored or solved badly. (I'd almost forgotten that horrible mess where I had no input on anything design-related, thank you for reminding me) -- Matt G / Dances With Crows The Crow202 Blog: http://crow202.org/wordpress/ There is no Darkness in Eternity/But only Light too dim for us to see -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Pros/Cons of using mysqli prepared statments
[snip] Just don't go too far. [/snip] I absolutely agree! Doing SP's for SP sake is not desired and be truly careful about cascading the procedures. And always, ALWAYS document your code and put copious comments in the SP's. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Pros/Cons of using mysqli prepared statments
One thing to remember is that dealing with results from prepared statements is different then getting results from queries, so if you are using both, confusion can easily set in (and lets face it, prepared statements arent always the best thing to use)... if its of any help, i have written a class to work around that, instantiate it with a query or result object from a statement and you get a uniform way to get the result array... http://pastebin.com/sAhZJcNX ~ Alex On Thu, Nov 4, 2010 at 5:38 PM, Jay Blanchard jblanch...@pocket.com wrote: [snip] Just don't go too far. [/snip] I absolutely agree! Doing SP's for SP sake is not desired and be truly careful about cascading the procedures. And always, ALWAYS document your code and put copious comments in the SP's. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Pros/Cons of using mysqli prepared statments
On Thu, 4 Nov 2010 02:48:55 -0500 Tamara Temple tam...@tamaratemple.com wrote: I'm wondering what the advantages/disadvantage of using prepared statements with mysqli are. I'm used to using the mysqli::query and mysqli::fetch_assoc functions to deal with retrieving data and bulding my sql statement in php code. Take a look at PHP PDO also. Tamara Temple -- aka tamouse__ tam...@tamaratemple.com May you never see a stranger's face in the mirror. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Pros/Cons of using mysqli prepared statments
On Nov 4, 2010, at 6:36 AM, Jay Blanchard wrote: [snip] If you have a query in your PHP code, which you are going to be executing a lot, even if you are using prepared statements, you can go one further by creating a stored procedure. Now the SQL server will only ever need to compile the statement once. No matter how many times it is used. You only need to supply the data which will be type appropriate. [/snip] I second this, using stored procedures has a lot of advantages. If you need to change your SQL you can do it in one spot. It reinforces MVS or modular coding behavior, the SP becomes very re-usable. Security is improved. Performance can be improved. You can put the bulk of the data handling on the database where it really belongs because SP's can perform complex operations complete with control structures. Lastly it simplifies your PHP code. (dangit, i sent this from the wrong address initially) I do know about stored procedures and have used them where appropriate (retrieving the entire contents of a table, one record from a table, etc.). It was the prepared statements that I haven't had experience with. I wasn't away that these were precompiled. That does make them more attractive for heavily executed pulls. On the other hand, the seem to require more intense maintenance than just changing some lines of code in a file if need be. (I assume prepared statements don't share the same efficiency of maintenance that stored procedures do across applications.) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
