PHP MYSQL adding security

Using Prepared Statement

[henry0]

This is another security tool not enough mentioned
Since it uses place holder it could too improve performances

Comments? [dev.mysql.com]

[eelixduppy]

Thanks for that, Henry, that's something that is very useful.

Just as a side note, even though MySQL will automatically "escape" special characters in a prepare statement that does not mean you should not be validating and cleaning your user input. This should be done all the time, even if you know someone wrong will "never" happen.

[whoisgregg]

I find the mysqli sample code [php.net] to be confusing...

Isn't the point that the DB admin would prepare these statements ahead of time and then the PHP code just references the name and passes the variable? If you are preparing it in the same place as you would otherwise generate a direct SQL statement, what's the difference?

[henry0]

I am now toying with the zend one; quite clear [devzone.zend.com]
but there is also a way around that I did not try yet
(if you may not get the mysqli ext on)

<<<<
PREPARE my_query FROM "SELECT myfield FROM mytable WHERE whatever =?";
SET @myparam = "somethingelse";
EXECUTE my_query USING @myparam;
DEALLOCATE PREPARE my_query;
>>>>

[henry0]

gregg, it's pretty much (it's my understanding) in the stored procedure genre

say you have 20 calls to the same qry
each calls is a statement S
each input Ipt
as is your total is Ttl=20*(S+Ipt)same as 20S+20Ipt

But using prepared statement will only regard the "different input of a same statement"
so the new ttl will = new_ttl=1S(only)+20Ipt

[edit]
you may not use any form of escape (it's up to you!)
it helps you verifying that the data passed are in the expected form
by using: bind_param()
that will look for data expected such as int, double, float, blob etc...
[/edit]

[whoisgregg]

Interesting. It looks like from one of the comments on the PHP manual page [us3.php.net] that the performance gains for looped queries can be quite significant.

Although I still have some questions about how it does it's data validation like, what happens if you bind a parameter to a decimal and provide a string, what happens? An error? A warning? If you've bound five parameters and one is wrong, does it provide a specific error for that parameter or does the entire execute just fail? Anyone know off hand?

Also a comment here [php.net] suggests that bind_param has trouble handling null values. Which makes sense, if all params are required to be one of string, integer, double or blob.

Clearly, some testing is in order. :D Why'd you have to get me all curious on a day that I had a lot else going on? ;)

[henry0]

I asked myself the very same question :)

here is my first problem

$mysqli = new mysqli('localhost', 'root', 'bbbb', 'my_db');

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$stmt = $mysqli->prepare("INSERT INTO users VALUES (?,?,?,?)");

$stmt->bind_param('sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;
/* execute prepared statement */
$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

$stmt->close();

bind param results in:
Fatal error: Call to a member function bind_param() on a non-object in C:\wamp\www\test\sql\demo.php on line 23

I conn fine using mysqli
and this comes from the code source
so what's wrong?

[henry0]

This works fine

gregg check your mail, I sent you a link (not sure I may post it here)

<?php
$mysqli=new mysqli('localhost','root','','cms');
if(mysqli_connect_errno()){
trigger_error('Error connecting to host. '.$mysqli->error,E_USER_ERROR);
}
// run simultaneous queries
// check how multi_query() performs
// looks like mysqli uses a bunch of functions [php.net] we need to get accustomed to

// such as: more_results(); next_result(); store_result; bind_param and more...
$sql="
SELECT * FROM users;
SELECT name FROM users";

if($mysqli->multi_query($sql)){
do{
// store first result set
if($result=$mysqli->store_result()){
while($row=$result->fetch_array(MYSQLI_ASSOC)){
echo $row['id'].' '.$row['name'].' '.$row ['email'].'<br />';
}
$result->close();
}
// echo some label
if($mysqli->more_results()){
echo 'echo second query...<br />';
}
}while($mysqli->next_result());
}

// close conn if script stops here, but there comes a second example!
// close connection
//$mysqli->close();

// for example there is a username named peter

$username='peter';
// prepare query
if($stat=$mysqli->prepare("SELECT email FROM users WHERE username=?"))
{
// bind parameter to marker
$stat->bind_param('s',$username);
// run query
$stat->execute();
// bind result set to $email variable
$stat->bind_result($email);
// fetch result
$stat->fetch();
// display row
echo 'Username '.$username.' email address: '.$email;
}
// close connection
$mysqli->close();
?>