masterlayouts
-
Posts
64 -
Joined
-
Last visited
-
Days Won
1
Posts posted by masterlayouts
-
-
mysqli_stmt_execute($stmt) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_stmt_errno($stmt) . ": " . mysqli_stmt_error($stmt));
This seems to work fime. Do I have to modify something else in your function?
-
1.
it is correct, but i will end up with a numeric array. i want to get an associative array as being consistent with names in forms, variables and datbase help me a lot. i will re-format the question: is there a better way to end up with an associative array from fetched data with prepared statements?
2.
the second part of your answer is intringing.
When should someone bind results and when not?
Is this a matter of preference or there is a golden rule or standard?
What is the best practice?
How this will measure with buffering results with mysqli_stmt_store_result? I have thousands of records... what should work better?
(Obviously the second part do not address the question as it has nothing to do with prepared statements... but it got interesting...)
-
i usually keep a separate page with the delete actions (delete user, delete posts of that user) for a specific task (delete user from forum) and simply run two separate queries, the second based on the success of first. This way I avoid using transactions...
It seems simple, not sure if it is the best thing what can be done...
I understand the transactions have a downside. How about database triggers?
At the end of the day probably all perform the task and it is not such a big deal (or it is)?
-
What is the fastest and easiest way to move the fetched data into an array using prepared statements?
Everything I do seems ad-hoc.
... we bind results in two variable $col1 and $col2 ...
$x = 0; $myarray = array();
while(mysqli_stmt_fetch($stmt)){
$myarray[$x]['col1'] = $col1;
$myarray[$x]['col2'] = $col2;
$x++;
}
...
Now we have a multidimensional array $myarray (what I want).
However, if I have more variables seems a little inconvenient to repeat over and over again... I wonder if there is any way faster or better.
-
Can regular queries can be mixed with prepared statements?
If we decide to use prepared statements should we stick with them for every task, even if can be done faster or easier with regular queries? For example in your book when you check if a user is registred or not or if we populate a drop-down menu from database... The first example assume interaction with the user, the second example does not.
I would be interested not only if it is possible (I guess it is possible) but what constitues the best practice? Start with prepared statements stick with them?
-
In chapter 5 you are talking about deleting data and the example to delete a record is:
"DELETE FROM users WHERE user_id = 8 LIMIT 1"
You never get to talk what happens with the posts of this user from the table "messages".
I assume the query to delete the user should reflect this situation as there may be situations (unlike a forum) where we want to delete everything related to that user.
Should we create two separate queries in order to verify the actions (hence the "LIMIT 1") or we pack everything in one query, I assume something like:
"(DELETE FROM users WHERE user_id = 8 LIMIT 1) AND (DELETE FROM users WHERE fkuser_id=8)"?
What will be the best way to do it considering factors like we may want to verify the action was performed succesfully or not and we may use prepared statements or not.
-
The custom error handler function as defined in your book:
function my_error_handler($e_number, $e_message, $e_file, $e_line, $e_vars) {
$message = "An error occured in script '$e_file' on line $e_line: $e_message\n<br />";
$message .= "Date/Time: " . date('d-j-Y H:i:s') . "\n<br />";
$message .= "<pre>" . print_r($e_vars, 1) . "</pre>\n</p>";
if (!LIVE) {
echo $message;
}
else {
@mail(ADMINEMAIL, 'Site Error', $message);
if($e_number != E_NOTICE) {
echo "<div id=\"error\">A system error occurred.</div>";
}
}
}
set_error_handler('my_error_handler');
It may handle errors as they happen in something like this one:
$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " .mysqli_error($dbc));
My question is: How about the prepared statments? Can be something like:
mysqli_stmt_execute($stmt) or trigger_error("Query: $q\n<br />MySQL Error: " .mysqli_error($dbc));
or should we use mysqli_stmt_error($stmt) and mysqli_stmt_errno($stmt)? Or maybe it is not necessary to do anything? Bottom line: how do we handle errors when using prepared statements?
Thank you.
-
1
-
-
You mentioned prepared statements as an alternative to mysqli_real_escape_characters. Does this mean that we do not have to validate/sanitization of the user input if it goes to a prepared statement?
It is my understanding that mysqli_real_escape_characters escape database specific characters such as NUL (ASCII 0), \n, \r, \, ', ", and Control-Z (according to the PHP Manual). So it makes sense to use it instead of something simpler like addslashes(). It also does make sense to use other things like strip_tags() and htmlspecialchars() to avoid other kind of problems.
What is the deal with prepared statements? The PHP Manual says:
"The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)."
I am sorry, but I have some difficulties to put everything together. Can the developer be sure or not, it seems to me rather as a contradiction...
-
I am using inbound. I am running PHP Version 5.3.5 as an installation of XAMPP for Windows version 1.7.4. + Apache 2.2.17 + MySQL 5.5.8 (Community Server).
...
$name = "Ford";
$q = "SELECT id_tst, name_tst, price_tst FROM test_tst WHERE name_tst=?";
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 's', $name);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
if (mysqli_stmt_num_rows($stmt) == 1) {
echo "we have a match";
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
mysqli_close($dbc);
...
On my machine, as reported, if I comment out mysqli_stmt_store_result($stmt) the example will fail to print "we have a match".
Thank you.
-
Thank you for your replies.
I guess there are several ways of doing the same thing or at least there are several tools in PHP that may be used for the same thing. Here I was thinking to validation.
We have ctype function, validation filters (like FILTER_VALIDATE_EMAIL), regular expressions and we can also use typecasting where needed.
Now if I use the typecasting for integers like when related to a request for a primary key, I see no point of using something like FILTER_VALIDATE_INT or ctype_ digit.
If I have to validate something like the email I can use FILTER_VALIDATE_EMAIL or a regular expression. The first it is not particularly faster than the second. So no need for FILTER_VALIDATE_EMAIL, the regular expression will do the job.
Now FILTER_VALIDATE_REGEXP is way slower for any decent regular expression than preg_match on the same regular expression. So it makes no sense to use FILTER_VALIDATE_REGEXP.
Two issues here:
First: I fail to understand why ctype and validation filters... Almost everything fails between the cracks.
Secondly what is the best practice for the following real world example. A field like First Name for example can contain characters like ' and - and may be persons that want to use . like P. J. D'Alberto-Johnson. Addresses may contain even more like # or ( and ) and a phone number may contain + for country code. What it is supposed to do in such situation? Validating with someting very general like ctype_ print at the risk of having names that may wongly contain + and phone numbers that may wrongly contain ' because of the user input?
In almost no real situation the ctype and validation filters resolve the problem. My question is: what is the best practice under the circumstances? Using many regular expressions to account for (international) names and addresses for example or just making sure there is something there and that is not a security problem?
Currently I am using regular expressions just for email and password, for all the other fields I through the ctype_print and I perform the heavy validation with ajax. If data goes in the database I escape it. Is this a good aproach or there are better options out there that I overlooked?
Thanks again for your time.
-
Hi Larry,
Greg again. Once more my apologise for posting question on the comments forum of your book. At your suggestion I moved them here:
I’ve noticed that you suggest the use of ctype and filter. However many people noticed errors with internationalization. I personally am inclined to use regular expressions to blacklist, whitelist and validate. I (recursively) eliminate the control characters from all user data than resolve the magic quotes issue than validate than typecast the result than filter with mysqli_real_escape_characters if it has to go to a database. Is this a redundant effort?
For example if no control characters are allowed from beginning, no chance of mail injection (like your “scrubber” function on the other book to deal with the mail injection).
Than a \p{L} will assure me, for example, that I will have only letters in any language or just from a particular language like \p{Thai}. Is this approach bad? What benefits could possible give me ctype and filter compared with a class that performs what I mentioned? Basically regular expressions against new (and still buggy at this time) PHP functions. Thank you and I’ll appreciate if you’re kind to answer.
-
On the presentation of prepared statements I've noticed an issue (at least on my machine):
I prepare the statement, I bind the parameters, I execute the query, than I check for a match:
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 's', $name);
mysqli_stmt_execute($stmt);
if (mysqli_stmt_num_rows($stmt) == 1) {
echo "we have a match";
}
This does not produce the expected result. However, it will work if I add after I execute the query:
mysqli_stmt_store_result($stmt);
Is this an issue with my machine or something overlooked at the time of writting the book?
Thank you,
Greg
-
Hi Larry,
I'm Greg and I wrongly addressed some question on the "comments" section of your book. Apologise. At your suggestion I address my questions here.
You created and refactor several times the registration, login, logout scripts… however, there is not even one page to serve as example of page for authenticated users. The solution suggested is to check for the presence of a session variable. Will this be safe enough? Should we change the default name for the session or regenerate the session it? Will help to have a nonce system implemented? Will help to encrypt some session variable (with salt) and check for that value? In my opinion such a sample page should have been present, namely: restrict access to a page for authenticated users. I would be interested of what you’re thinking about this subject. I’ve noticed the common way of thinking is to check for a session value like “first name” (not the ID or email as these could provide important information to an attacker). If there is no session value for first name than the user is not logged in. It seems very simple to me. Somebody may easily guess the first name’s session name (could be something like $_SESSION['first_name'] and generate a PHPSID=something and it looks the attacker have got access to the page without being in fact authenticated. In my opinion you should have finished what you’ve started (user authentification module) and create a good script tackling this matter (with session timeout, regenerate session id at minimum).
Thank you,Greg
Fetch Data With Prepared Statements In Multidimensional Array
in PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide (3rd Edition)
Posted
In fact prepare statements work in PDO as well. I wouldn't mind to understand how and why biding results in prepared statements and how to fetch the result in an associative array using prepared statements PDO style.
Is not the object oriented syntax the issue, but prepared statements.
I appreciate your time and effort, but your code does not make use of prepared statements. I know how to fetch data in an array OOP or procedural. What I would like to understand is when and why it makes sense to bind the results (I guess when the result is re-used in another query, but as I do not have enough experience I do not know if this is the right answer, just an assumtion I make).
Also the PHP manual says:
"The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)."
This is what makes me make the assumtion above, however I would like to know what other peoples think and maybe some veterans could help me understand better the issue.
I had a post regarding the quotetd statement above... I am very interested to know when prepared statements will fail.
If prepared statements are so wonderful and there is no way of SQL injection and no need to sanitize the data at least for SQL statements becase the prepared statements take care themselves of the matter... I am wondering why would somebody, even a beginner, want to learn and use something else?
There should be a reason why NOT to use prepared statements?
Thanks again for your help.