ajoo

Hi Kicken. Thanks for the clarification. However, after some testing, I do feel that if there are a number of conditions that the query must handle it would result in a rather awkward solution. Is there by any chance a better way to handle such queries? Thanks again !

Hi Guru Barand, Thanks loads for that. The problem is that $pp and $ll in the original query were handling the Query to deal with different conditions where the strings created different conditionals. for example : $pp = "(ms.level = 'Beginner' || ms.level = 'Intermediate')"; $pp = "ms.level = '$dd_level'"; // where dd_level can be one of the three values 'Beginner', 'Intermediate' or 'Expert' Similarly the value of Diff can be <7 or it can be any one of the values from 1 to 7. so $ll = "ms.Diff <= 7"; or $ll = "ms.Diff = 3"; Thus the same query was able to handle so many situations. How can I achieve something like that here using prepared statements. Thanks very much.

Hi all !, I am stuck on the following piece of code which does not give an error nor does it give a result. ( i.e. it gives 0 num_rows which should be > 1). If, however, I execute the query in phpmyadmin by simply substituting the values of $pp,$ll and $room_no in the query it gives the correct result. Please can someone tell me what I may be doing wrong here. Thanks ! $fcon = mysqli_connect($db_host,$db_user,$db_pass,$db_database) or die('Unable to establish a DB connection'); $pp = "(ms.level = 'Beginner' || ms.level = 'Intermediate')"; $ll = 'ms.diff <= 7'; $room_no = 4; $query = "SELECT md.Member_reg_id, md.fname, md.lname, md.email, md.cell, ms.level, ms.diff, ms.score, r.ID_Status FROM register as r JOIN member_detail as md ON r.ID = md.Member_reg_id JOIN memstatus as ms On r.ID = ms.ID WHERE r.CENTERCODE = ? AND r.ID_Status ='A' AND ? AND ? ORDER by level, diff, score DESC"; $stmt=$fcon->prepare($query); $stmt->bind_param('iss',$room_no,$pp,$ll); if(!$stmt->execute()) die('Failed to execute the query'.$fcon->error); else { echo "Executed"; $stmt->bind_result($Member_reg_id,$fname,$lname,$email,$cell,$level,$diff ,$score,$ID_Status); $numrows = $stmt->num_rows; $stmt->store_result(); // echo $numrows; while($stmt->fetch()) { echo "<br>".$fname.' '.$lname; echo "<br>".$level; echo "<br>".$diff; echo "<br>".$score; echo "<br>".$cell; echo "<br>".$email; } }

Thanks again !! Just wanting to clear all doubts.

Hi Jacques, Thanks for that once again. Such an oversight on my part. I thought it was store_result() instead of bind_result and fetch() that was to be executed outside the function. It has worked. Thanks for the advise on switching to PDO. I will keep that in mind but will reserve that for later. I have since your advice on filtered variables changed all files to mysqli prepared statements. I don't have the heart or the time right now to change it all once again. But I will keep it in mind. My functions is correct with the values being passed like you mentioned. Thanks very much ! Much obliged.

Hi Jacques, Thanks once again for this reply. I have just tried out the second method you described above. To use the raw stmt returned by the function. I still get nothing but a null array. Maybe I am misunderstanding what you mean by raw statement. here is what i did function display_all{ $query = "SELECT one, two, three four, index1 FROM numbers WHERE index1 = ? LIMIT 0, 10"; $stmt = $conn->prepare($query); $stmt->bind_param('i',$var) if($stmt->execute()) { $stmt->bind_result($one, $two, $three, $four, $index1); } return($stmt); } bound the result and returned $stmt. And in the main $stmt->store_result(); $stmt->fetch(); but i still get null values. Kindly guide. Thanks

Hi Thanks Jacques, All the information provided by you has been very valuable. There are so many answers that can be marked as best but I would like to mark the one just above this as the best since it answers most of the original question about validation of constraints. However I would just like to ask once again and in the context of the examples above, that would it not be OK as to use the the inbuilt php validation function for checking the 'age' ? Thanks !

Hi all ! I have a piece of code here: $result = display_all(fcon, $var1, $var2); function display_all( // defined in another file $query = "SELECT one, two, three four, index1, index2 FROM numbers WHERE index1 = $var1 LIMIT 0, 1"; $result = mysqli_query($fcon, $query); return ($result); ) and then I use the returned variable $result to display the value as follows:- while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { echo "<tr>"; echo "<td>".$one."</td>"; echo "<td>".$two."</td>"; echo "<td>".$three. "</td>"; echo "<td>".$four. "</td>"; echo "<td>".$index1. "</td>"; echo "</tr>"; } and this displays the n rows of data returned. Now I have started using prepared statements and the function is now function display_all{ $query = "SELECT one, two, three four, index1 FROM numbers WHERE index1 = ? LIMIT 0, 10"; $stmt = $conn->prepare($query); $stmt->bind_param('i',$var) if($stmt->execute()) { $stmt->bind_result($one, $two, $three, $four, $index1); $stmt->store_result(); } return($stmt); } However the returned $stmt object is unable to display the n rows of data since it shows null values. I assume that this is not the right way to use the $stmt object to display data. I must be missing something. So I request you guys to help me with this. Thanks loads.

Hi Thanks all, Sorry I have returned here after some time. Thank you Jacques for all that information and the example of injection via email.I am changing the code to now use mysqli prepared statements. I still would like to implement the constraint validation though and on the server side. Since I always learn faster via an example I would once again request for a server side validation example for a variable that may have integer value from 1 to 100 and a string with a max length of 30 characters. ( to validate for 30 english characters and not 1000 chinese characters). Say for example a form that sends the name ( max 30 chareacters in length) and age (max 100 years). Thanks very much !

Hi Jacques and all, Thanks again for the reply and I have been reading and trying to understand what you are saying here. Actually I just want to validate data (not sanatize it) and mostly use filter_input validation functions but there is none for strings and so I had to use the SANATIZE in my example. Besides like I said earlier I was just looking to check / validate the the string length was within limits. One I would like to ask how is it possible to use a perfectly legal email ID to carry out any attack, SQL Injection or any other kinds? What I gather is that if we do not use any kinds of filters we are good if we use Prepared Statements for mysql queries. And we must escape all HTML output ( Strings ) with htmlspecialchars, htmlentities, etc other such functions. If we are doing this then there is no need to filter the input. Is that correct? Sorry if I sound so confused, but then security is an extremely confusing topic. And there is everybody - well almost - cautioning to use filters. Thanks for the answers. Look forward to some more.

Hi all ! Oh wow ! SO many replies. Hi Jacques. Thanks again for cautioning me to not use filter_input. I am dying to drop it but kindly suggest an alternative. Yes the charset is UTF-8. The goal is to have a safe user input in a form which has the following fields: 1. Userlogin which I wish to limit to 40 characters ( as also the length of VARCHAR in the database). That was the reason for wanting to use the string length. 2. Password. 3. Names 4. Address Fields. 5 Gender. 6. Phones 7. Cell Phones. 8. City, State and Country. 11. email As you can see all of these are required to be alphanumeric strings and some of them require characters like '.', '-', '+','_', and maybe some more. I would like to limit the length of most of these strings. For example I would like the phone string not to exceed say 13 characters. I am also using the inbuilt filters for INTEGERS, EMAILS and a REGEX for the gender field. So if FILTER_SANITIZE_STRING is wrong and I am sure it is if you say so & as also explained by you, I'ld like to ask you how should we filter these then to ensure that they are safe or at least have lengths within the ranges that we want them to be? And yes I am changing all mysql statements to mysqli prepared statements. Thanks again all for the replies and looking forward to some more.

Hi all, The output of the following line of code where user is Jack1234 $user = filter_input(INPUT_POST, 'user', FILTER_SANITIZE_STRING); using var_dump is string 'Jack1234' (length= I would like to know if there is someway we can use/retrieve the length value of the string that is displayed in the output or verify the string length against it. Thanks.

Yes Sir ! Thanks Loads.

Hi Guru Barand, Does that mean that we can simply use a higher value in the max_range in options array of the filter_var and it would do the trick ? Thanks again !

Thanks Guru Barand, That answers it all ! Have already changed the query to this form. Will include the active in the query too. Thanks for outlining the benefits of this structure.

Hi, Is there any way to use the inbuilt filter functions in PHP to filter for integer values greater than 256? All examples that I saw had a max_range of 256. Kindly suggest. Thanks.

Hi Ch0cu3r, Thanks for the reply and the suggestion. I will definitely make the switch. Would that be better from a security point of view or for any other number of reasons? I am asking so that many others like me would be clear about it. Thanks very much.

Hi, I have the following query SELECT user_details.User_club_ID, user_details.fname, user_details.lname, user_details.email, user_details.club_No club.CLUBCODE, club.club_id FROM user_details, club WHERE club_id = $cid AND user_details.club_No = club.CLUBCODE AND user_status = 'active'"; which I converted to a prepared statement as SELECT user_details.User_club_ID, user_details.fname, user_details.lname, user_details.email, user_details.club_No club.CLUBCODE, club.club_id FROM user_details, club WHERE club_id = ? AND user_details.club_No = club.CLUBCODE AND user_status = ?"; Please note that user_status is a field in the table user_details. The original query (non -PDO) works correctly. I want to know if this is correct and that the comparison in the WHERE clause i.e. user_details.club_No = club.CLUBCODE is security safe. If not then how should this be modified. Also if there is a better way to write this statement, kindly show that as well. Thanks Thanks all !

Hi Thanks Winston, That was good reading. Well I guess gaps are good. Guru Barand is always right. Thanks !

Hi Guru Barand, Thanks for the response. Well the missing id=7 doesn't matter really and the database will function correctly as you said. It's just that I feel that in a multi-user app it could result in a whole lot of lost ids and since the corresponding entries in different tables would have different values it could result in confusion especially if the number of entries is large. I mean if there is a way to keep the correspondence simple without much work then I would like to know and if it can be achieved in a simple manner I would like to implement it. Thanks loads for now and for so many times previously.

Hi everyone, I have an application that makes use of a transaction, inserting and updating values in different tables. One of the first tables it inserts into has an auto increment ID field. This ID values is used / updated in some other tables. Everything works fine. If however the transaction fails then the auto_increment values are upset - meaning that the sequence is jumped. For ex. if I had auto_increment values from 1 to 6 in this table and then the insert query fails for an auto_increment value of ID=7, then this value of ID = 7 is lost because the next time the query is run the auto_increment ID value will be equal to 8 and accordingly updated in the tables. ID value = 7 is lost. Is there any way to prevent this ? Please know that this is a multi - user application. Thanks.

Hi, Thanks for the reply. However that is not what I was looking for. I am using mysqli_real_escape_string on the variables of my login form and I want to replace that with input_filters. Can you please suggest what filter would be most apt for login and password fields on my form.I want the username not exceed 30 characters in length and both the fields to be SQL injection safe. I will use mysqli PDO for the database but I just want to validate my inputs at the point of entry into the program. I don't know if that's something redundant but I believe that it's good practice to validate inputs at the point of entry. At least that's what I have read on googled results. Thanks !

Hi all, I have been reading in almost everywhere that we should not use our own custom login and password validations ( like regex etc.) but instead use the filter_var and filter_input built in functions provided by PHP 5 and above. However even after searching for more than an hour for with different search strings, I have not found even a single example that shows how we may validate for a username/login and password in a login form. Can someone be kind enough to provide a strong secure validations for username and login. Additionally I would also like to clarify if the username and login fields in a Login form be manipulated in any manner to pose a security threat? I mean can a hacker craft a username/login or password in such a manner as to pose an injection or any other threat? Thanks all.

Hi Jacques, Thanks for that information. I use the mysqli as of now. I have to read up on PDOs. Will be a while before i use them though. I'll bear in mind this piece of information. Thanks.

Hi Psycho, Thanks for the response. Yea I am all mixed up reading security and related stuff, especially the ones you mention - sanitization and validation. I just mixed two issues by mistake. What I wanted to know was ( with an example) that if there can be security issues related to using a particular character set like UTF8. Any particular precautions that need to be taken when using UTF8. If there are any then please let me now. Thanks for the reply.