Regarding prepared statements, this is new to me so I went out and did some reading. I am a little confused in exactly how they are used. I understand the structure, execution, etc. but from what I have read, it appears that the prepare statement is issued at runtime, is that correct?
So if I have a query that is based on city and state values that a user inputs (select * from company where city = ? and state = ?), and this same query will be executed many times by many users based on different city/state pairs, do I execute a $cnxn->prepare statement every time in the php code? If so, is that something that the server has stored so when you are referring to it in the runtime php code, this is only a reference to something already set up on the server? Again, forgive the confusion, but if I have to execute it every time, where is the performance benefit?
Okay, I figured out how to dynamically build the bind_param statements using call_user_func_array. The only remaining question at this point is regarding the use of sql_calc_found_rows using prepared statements but i will open another thread for that one. Thanks for the help.
So for the in clause, it should be something like:
$parmcount = count($arrayvar);
$inclause=implode(‘,’,array_fill(0,$parmcount,‘?’));
$sql = “select fld1, fld2 from venue where fld3 in ($inclause)”;
$prep1 = $link->prepare($sql);
$prep1->bind_param( …
this is where I get lost, or maybe it was earlier, how do I build the bind statement? Can I dynamically construct that with an array_fill of ssssss something like
I’m understanding the advantages much better now, thanks.
Just one outstanding question at this point, how do I handle an in clause with multiple values that are assigned at runtime in a prepared statement. I don’t know how many values will be in there until the user gives me direction. select * from tbl1 where zipcode in (?, ?, ?, …) where I don’t know how many ? there will be?
All SQL statements are executed at runtime, prepared statements are likely cached, giving them an advantage over standard SQL statements. Because parameters are bound to the prepared statement, if you issued an UPDATE statement, such as:
UPDATE users SET fname = :fname
A prepared statement is not parsed and evaluated each time it’s invoked, unlike a mysql_query() invocation would be, because it has to be.
A prepared statement is evaluated once and new parameter values are used. I don’t really consider this much of a performance boom, except in cases where bulk importing is concerned. But it’s still has an advantage.
Another advantage over standard SQL statements, is security. Prepared statements use parameter binding. When the prepared statement is parsed all bound parameters are escaped and cast/converted to appropriate type.
You can effectively eliminate/minimize SQL injection exploits using prepared statements. You can of course easily circumvent this by relying on string interpolation, such as a query like:
SELECT * FROM table WHERE name = $name AND age = :age
Only the one parameter is secured automatically, you need to manually ensure the $name variable is properly sanitized and escaped, otherwise you may get a SQL injection exploit.
At least this is my understanding of prepared statements
A query, like SELECT * FROM table WHERE thing = ‘a’ has to be parsed by the database into something it can execute, this is where the performance hit (however small) is incurred. With prepared statements you would issue SELECT * FROM table WHERE thing = ? and this can be parsed once (prepared) with data being bound to the placeholder on successive executions - the interpreted instructions remain the same, the data is just replaced.
In reality, the biggest advantage is the placeholder itself: it means there is no confusion between what is part of the instruction, and what is part of the data. The classic example is SELECT * FROM table WHERE thing = ‘O’Riely’. This will obviously confuse the parser with three ’ instead of the expected two. With placeholders, there is no confusion as it translates to ? = the string O’Riely. This is what prevents injection, it is a side affect of just doing things properly.
