markifornia
-
Posts
112 -
Joined
-
Last visited
Posts posted by markifornia
-
-
It's actually worth taking a look at some of the well know eCommerce (virtue mart, magento, zen cart, os commerce, presta shop) and message boards (vbulletin, phpbb) setups, you can see in phpmyadmin how all their databases are setup and also the view the mysql datatype's they are using. That way you can can possibly get some tips from them on making your databases. Ive seen vbulletin have the salts inside the users table, the same as Larry is mentioning here.
Good advice, I have worked with zencart extensively though I hate working management systems, I hate upgrades!!!! But forced to do so for clients.
I'd be curious to see what these web apps are using to salt data.
-
I just looked this up in Larry's book, okay
!= (also <>) Both mean Not equal to,
There you go Hartley San.
I'm just chiming in here learning any way I can. But you can definitely use != operator in SQL statements. Larry's book page 271, I have actually used the method there to check if emails aren't already taken.
Here is the SQL statement:
$q = "SELECT user_id FROM users WHERE email="$e" AND user_id !="$id";
I haven't gone into OOP yet, but perhaps your issue lies here:
//bind param
$stmt->bind_param('is',$ID, $Email);
Have you tried binding them this way?
// Prepare
$stmt = mysqli_prepare($dbc, $sql);
// Bind
mysqli_stmt_bind_param($stmt, 'is', $ID, $Email);
You can start debugging this way.
-M
-
1
-
-
Yes, you do need to perform multiple inserts and a JOIN query here. If each salt is unique, you need to insert it into a salt table with the user_id/email/something unique as the foreign key.
INSERT INTO salt ( user_id, salt ) Values(1, 'unique-salt-for-user-id-1'); INSERT INTO user ( user_id, card_number) VALUES ( 1, AES_ENCRYPT('the_card_number', 'unique-salt-for-user-id-1') );When you need the salt again, you need to select the salt too:
SELECT user.user_id , AES_DECRYPT(user.card_number, salt.salt) as card_number FROM user as user INNER JOIN salt as salt ON ( user.user_id = salt.user_id) LIMIT 1
That's the general idea.
get the general idea here, the foriegn key is what would associate the records between the tables. great example you've given.
Or really, in that case, you'd just store the salt in the same table as the database being salted. That way, you don't have to do a JOIN. Or, you could just use another part of the stored record as the salt, which has the same effect.
thanks larry, this is absolutely genius. didn't occur to me to use just about any part of the data as the salt within the same table. brilliant.
-
I understand this is a very simplified example given in the book as there only consists of few records.
encode table - contains the id and card number
aes_salt - contains just the salt
On page 387, the instructions for decrypting a stored credit card number shown below:
SELECT @salt:salt FROM aes_salt;
SELECT id, AES_DECRYPT(card_number, @salt) AS cc FROM encode;
This is assuming we are working from just the books example, but what if there were two salt values and two credit card numbers? I am assuming the way to assign each salt with each credit card between two tables is to use a foreign key?
How would you go about running the SELECT query above using multiple records? For example if I had multiple credit card numbers from the encode table and multiple salts in the aes_salt table?
Would the salt table be already pre populated with salt values??
Here is my taking a stab at it. Assuming my theory is correct about the foriegn key, aes_salt will have an id column which is an fk of encode.
Step 1:
SELECT @salt:=salt FROM aes_salt;
INSERT INTO encode (card_number) VALUES (AES_ENCRYPT(123456, @salt));
Step 2:
SELECT @salt:salt FROM aes_salt WHERE id=2';
SELECT id, AES_DECRYPT(card_number, @salt) AS cc FROM encode;
it just seems as though there needs to be a foreign key and a WHERE clause to make this work with multiple records.
Any advice appreciated.
Thanks,
Mark
-
That while loop syntax also confuses me, even though i know a query is unrelated. So why does the while loop go through all the rows in an array when the $row condition is equal.
I checked the return value on php.net for mysqli_fetch_array and it says Returns an array of strings that corresponds to the fetched row or NULL if there are no more rows in resultset.
Edward, my example comparison above wasn't the greatest of examples as Larry even pointed out. I had added a WHERE clause which would make the while loop unnecessary I presume. I too am getting the hang of it.
But to take a stab at your question, $row isn't equal to something, $row is a variable that is assigned the results of mysqli_fetch_array that loops through each row even NULL values.
Anyone? Correct me if I'm wrong.
-Mark
-
Prepared statements makes total sense, except the example in the book does not include a while loop. See my comparison below, I assume they would be very similar.
In regular queries we use:
$q = "SELECT user_id FROM users WHERE user_id = '$id'";
$r = @mysqli_query($dbc, $q);
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
// print out array
}
But in prepared statements would it be like so?
$q = "SELECT user_id FROM users WHERE user_id = ?";
// Prepare the statement
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_execute($stmt);
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
// print out array
}
** I'm assuming the while loop goes after the mysqli execution.
Is this correct?
Thanks,
Mark
-
If you're testing this locally, without a lot of traffic, your sessions may not be expiring because garbage collection isn't kicking in often enough. Here's how it works: with every invocation of PHP, there's an X% chance that garbage collection will be performed. The X is set in the configuration file, but I think is 1 by default. This means that 99 requests out of 100 ARE NOT triggering garbage collection. Furthermore, these would have to be requests that aren't also extending your session. If you're testing locally, or an a server without any traffic, you're probably just not triggering it.
Exactly what I was looking for Larry. Page 354 explains "Garbage Collection". I am running my site on a server that does not receive a lot of traffic, it is a management tool for just a few internal admins, so garbage collection isn't kicking in.
This section of the book explains it well, just now found it - thanks!
-
Yes, the session creates a cookie which stores the session ID. This is safe for the user to see and it's rather difficult to hack.
The problem is simply this: if a site just stores the user_id in a cookie, it's very, very easy for anyone to edit that cookie and change it from say, 1, to 2. Now the user is pretending to be someone else. But if that user_id is stored in a session, one user can only pretend to be another user if they can falsify the session ID. That session ID is 32 hexadecimal characters, which is unlikely to be hacked, and not worth the effort.
I think the ENUM issue is separate and unrelated to this specific issue.
Great thanks I fully understand now in regards to my question. Since we are on the discussion of sessions(), I have an extra question below:
My web application works great, except the session never expires if left overnight I am still in the session because I have not logged out. But I would like a session to expire based on idleness length of time.
I have been searching chapter 11 for a way to designate an expiration on a session() if the user has been idle for a specified amount of time. I have forgotten where this line of instruction is located now, or if it only applies to set_cookie().
Thanks,
Mark
-
Doesn't the session create some sort of identifiable cookie on the user's system though?
My question also. The session automatically creates a cookie correct?
Yes and Admin on a major site can have user_id of 0 or 1 in the database, so people know what to look for. I like the ENUM data type so you that way you can distinguish between a member and the administrative side.
I've never used ENUM, great suggestion. But if storing a user_id in a session is OKAY, then I would rather proceed that way.
-
Yes, if you need the iD. store it. The problem with Using user id is that everyone knowns numbers. If you use usernames or email adresses, you won't have that problem.
Yes, in my web app I need the user_id both stored in a session and database. I dont understand why user_id will be a problem but usernames and email addresses won't be a problem. Don't they have same level of vulnerability if stored in a session?
-
I am trying to understand this bullet point on page 368:
"Watch how database references are used. For example, if a person's user ID is their primary key from the database and this is stored in a cookie (as in Chapter 11, "Cookies and Sessions"), a malicious user just needs to change that cookie value to access another user's account".
Page 349 tells us that when a session is created, it also creates a browser cookie.
For example when tracking user activity, checking to see which user modified a table.
Here some methods in the book:
(1.) A solution on page 358:
Page 358 provides a method for improving sessions security by checking if the $SESSION['agent'] is set, and then also checking if the the $_SESSION['agent'] matches the $_SERVER['HTTP_USER_AGENT']
(2.) A solution on page 360:
Page 360 introduces session_regenerate_id(), which provides a new session ID.
MY QUESTION:
While these are nice methods to use, should we not store a users ID like so?
$_SESSION['user_id'] = $data['user_id'];
Here, the users primary ID is stored into the session. I am thinking of plenty scenarios where this is necessary like tracking user activity. I have already built a nice database design and script that uses the users primary id as a way to track their activity.
Is this the right way to do it? Is this unsecure?
Thanks,
Mark
-
Thanks for clarifying Edward. that confirms my understanding of the list() function.
-
1
-
-
very useful, thanks antionio.
-
Script is awesome, working great.
I am just trying to understand this function the list() function used in conjunction with the custom
check_login() function (this function is created in includes/login_functions.inc.php)
The() list function in this script takes on two parameters or returned values. Does this mean that the check_login function needs to return exactly 2 values?
What if there are 3 values returned by the check_login function, would that mean the list_function() would need a third parameter?
If check_login returned 3 values, then would it be like so:
list($check, $data, $more_data)
Thanks
Mark
-
-
thanks larry ill do some debugging with that.
after concatenation as shown in above step, can we assign that superglobal to a variable like so:
$fn = $_SESSION['fullname'];
-
session_start();
$_SESSION['fullname'] = $data['firstname'] . $data['lastname'];
I am getting an undefined variable when I call on $_SESSION['fullname'];
Can this be done?
Thanks,
Mark
-
-
My web application is going along smoothly thanks to both of you.
I am workin on webe site and in my database design i added those fields to my tables
CreationDate, LastEdited, CreatedBy and LastEditedBy.... the createdBy and LastEditedBy are fk to the user table.
When an admin make a changes to a record in a table, I use the userID in the session to add the userID to those fields
for example, if the admin Bahaa make a changes to x record in the category table I add the userID for bahaa in the LastEditedBy fields.
If you still don't understand any point let me know and I will be happy to help you.
Bahaa, When updating the table containing CreationDate and CreatedBy these values will remain the same. How do you make a query update if they will have the same values. CreationDate and CreatedBy will not change during an query update, therefore are these fields skipped?
Thanks,
Mark
-
Thanks bahaa, exactly what I was looking for.
Larry mentions above that there are proper headers while retrieving an image, which one did you use?
-
I would like to add that I prefer your method. I am working on building a very simple script for it.
-
got it. you are using a combination of the two. are you getting errors with the file name extensions being included when inserting into the database?
When you call your images, do you call them from the database or the directory?
-
Thanks Larry i was just curious if it would be sufficient using one method, or if using both methods is more preferred? I am looking for the most practical and popular solution.
Bahaa - thanks for the script, Larry's book provides a similar example. Yours doesn't include an INSERT query though. Are you also inserting the image into the database, at the same time inserting it in the uploads directory? And if so, then why did you have to use both methods.
-
I was able to code this script without any problems, I understand the process of uploading a file from apache's default temporary directory to the uploads folder.
My question is:
Is this tied to inserting an image into a database? Or an alternative method? If I wanted to tie certain images to a certain users unique id, it seems like uploading a file into a directory wouldn't work.
If inserting an image into a database is the solution to my question, would the correct datatype be LONGBLOB? Again, I'm just guessing after some research this seems to be the solution.
Thanks
Mark
Chapter 12 Page 387 - Database Stored Salt
in PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide (3rd Edition)
Posted
got it larry. i was thinking of generating a unique set of strings via php functions which could be the salt