Basic PHP / PDO Question

I'm a new come to PDO and I want to better understand how PDO handles "escaping" characters that would otherwise prove problematic.

With mysqli, I've got a string that I'm getting ready to insert into my database and I would "prep" it using something like:

$headline = mysqli_real_escape_string($cxn, trim($_POST['name']));

With PDO, I'm thinking that funcationality is being handled by the dynamic that's included with "bindValue"...

$mssql_stmt_11->bindValue(':headline', $headline, PDO::PARAM_STR);

Correct?

If that's accurate, then what happens when you're using something like this:

$mssql_stmt_11=$mssql_pdo->prepare("insert into tmp_Results_1
(str_Series,
str_StatType,
int_Value,
int_StatType,
str_ProjectName)
VALUES
(?,?,?,?,?)
");
$mssql_stmt_11->execute(array($series, $stat, $int_value, $int_stat, $project_name));

I really like the last example, just because there's a lot less "typing" and with that less opportunity to make a mistake, although you do need to keep close track of the order in which you document your variables.

But how is the data being "prepped?" With the "bindValue," it would appear it's occuring with the PDO::PARAM_STR (what do you call taht by the way?). But how is it happening when you're using the question mark placeholder / array approach?
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott MadeiraCommented:
When you use the array approach all parameters in the array are treated as strings and are escaped accordingly.  If you have a mixture of ints and floats and strings then you need to use the appropriate bindvalue for each parameter as you show in your first example.

Here's the link to the PHP manual page: http://php.net/manual/en/pdostatement.execute.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Slick812Commented:
greetings  brucegust, , you ask - "that functionality (string escape) is being handled by the dynamic that's included with "bindValue ?"

But this is NOT true, The Prepare() and Execute()  uses an entirely DIFFERENT chain of operations to get the SQL syntax, and then the DATA to the MySQL database engine.
In the old way of using just the  
     query("select id, name from names where city ='$city'")
Everything, as the SQL syntax, and the DATA ($city in above), are combined into a single string and sent to the MySQL engine.
In the prepare - execute, First ONLY the SQL is sent, no data -
   prepare("select id, name from names where city = ?")
please notice that there are NO single quotes around the ? in the prepare, as in the previous around the $city .
You do not have to define any strings in a prepare DATA, because it is only looking at the SQL syntax with prepare().

when you use the  execute()  then a Second database operation takes place, and sends JUST THE DATA, no SQL, , so you never need to escape the DATA in the  execute(), because the escaping is for a "String" insertion, and to parse that data "string" out of the SQL string later.

You ask - "But how is the data being "prepped?" With the "bindValue,"?
The data segments are not changed in any way, because it is never inserted into a String, but keep separate, for better performance and safety.

I do not use the PDO for MySQL, if I can avoid it, there were many problems with the way that PDO tried to change over to the newer mysqli "Drivers" in PDO code. Even in the php ver 5.4 , it will default to using the old way of emulating the prepare - execute in mysqli, where is just does the SQL string insertion, and not the prepare().
I have to add this -
    $dbPDO->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
or else it does not do a real prepare and execute.
0
brucegustPHP DeveloperAuthor Commented:
Excellent explanation, Slick! Thank you very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.