Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hi Antonio,

Does this fit the bill?

//check for email
if(empty($_POST['email'])){
$errors[] = 'You did not enter your email.';
}else{
$email = mysqli_escape_string($dbc, trim($_POST['email'])); //this is escaped bit
//make a query to check if email is already in use
$q = "SELECT user_id FROM users WHERE email = '$email'";
$r = @mysqli_query($dbc, $q);
$n_rows = mysqli_num_rows($r);
if ($n_rows > 0){
$errors[] = 'This email address is already in use.';
}else{
$email = mysqli_escape_string($dbc, trim($_POST['email']));
}//end if email already in use

}//end if empty email

Thanks for your taking the time to check this for me. I tend to learn much from what I get wrong and it really helps when someone points out my mistakes.

  • Upvote 1

Share this post


Link to post
Share on other sites

Thanks for the suggestion. I have altered all the relevant code in my IDE to help me remember that.

I now make my offering for Q4 page 298 using mysqli_affected _rows(). I have just posted what I believe to be the relevant bit. Hope I got this OK.

 

//make the query
$query = "INSERT INTO users (first_name, last_name, email, pass, registration_date)
VALUES ('$fn', '$ln', '$email', SHA1('$password'), NOW() )";

$request = @mysqli_query ($dbc, $query);//run the query
if (mysqli_affected_rows($dbc) == 1){//if it ran OK should be 1 row only changed
//display message
echo '<h1>Thank you!</h1>
<p>You are now registered. In Chapter 12 you will be able to (gasp) log in!</p>
<p><br /></p>';

 

I am going to mess about with queries for the next couple of weeks as mentioned in the final part of the Review and Pursue ch9 because I can use the practice!

Share this post


Link to post
Share on other sites

Looks like it should work, but you are forgetting to escape data again. All queries that includes input from a user MUST have it escaped. The problem is SQL injection, which is a serious security hole. Right now, I can easily delete your user table.

Share this post


Link to post
Share on other sites

Hi Antonio,

Thanks for looking in again.

Earlier in the script each of the user inputs are treated thus:

 

//check for first name
if(empty($_POST['first_name'])){
 $errors[] = 'You forgot to enter your first name';
}else{
 $fn = mysqli_real_escape_string($dbc, trim($_POST['first_name'])); //validate user input
}

 

Is this enough or should there be further checks on input?

I have seen forums where prepared statements are recommended, but that looks a bit advanced for this forum _ and for me _ as yet.

Share this post


Link to post
Share on other sites

Yes, that should do the trick. I see no problems there. Maybe a strip_tags() should be applied though. I recommend you to read up on XSS attacks when possible, but at least your DB is secure now. (The problem being that inserting HTML tags like <script> is possible) One step at the time. :)

 

Prepared statements are generally preferred as they make SQL injection impossible. They are a bit tricky in the beginning, but you should really get the hang of it pretty quickly. With proper validation, like you have applied here, prepared statements and normal queries are pretty much equally secure. The benefit of prepared statements is that they won't work without a comparable validation to what you've applied here. That's really the strength of it.

 

I think Larry does prepared statements in some of his books. There should be more than enough examples online to learn it though. I wouldn't rate it as a priority, but it could definitely be learned pretty quick if you wanted to.

Share this post


Link to post
Share on other sites

Just going back through my chapter Reviews & Pursues of PHP and MySQL for Dynamic Web Sites Fourth Edition before purchasing the new book, and I have one hangup on a Chapter 7 Review question:

  • Why doesn’t the forum database support transactions?

 

 

I'm not sure how to answer this. Previously in the chapter, it says that in order to perform transactions, "you must use the InnoDB table type (or storage engine)." [pp. 234, para. 4] Well, the question references the "forum database", and I didn't think entire databases had a specific storage engine type, only the distinct tables within. Following that, the forums table does use the InnoDB storage engine, so why would it not be able to utilize transactions? Or am I totally skewed? Thanks in advance for the assistance!

Share this post


Link to post
Share on other sites

Only the E commerce example in chap 19 uses the transactions, the forum demo was just a basic example it doesn't incorporate those. Yes you are right you must use InnoDB tables to perform transactions. Check out pages 654 onwards.

Share this post


Link to post
Share on other sites

Because its just a basic example of how PHP and MySQL works, the book wasn't written to help you specifically build professional forums like vBulletin or phpbb. It would be more concrete anyway to be build your site into a PHP framework to give you more leverage.

  • Upvote 1

Share this post


Link to post
Share on other sites

Actually, the answer is that it can't because it uses the MyISAM table type, which doesn't support transactions. And I chose that table type in order to support FULLTEXT searches.

Share this post


Link to post
Share on other sites

OK, I'm back to being thoroughly confused. Referencing Chapter 6, pp. 186, steps 2 & 3, step 2 creates the forum database, which does not take a storage engine as an argument in its creation, so that doesn't jive with the wording of the thirteenth review question for Chapter 7 I quoted above. Step 3 creates the forums table, and the engine argument given is InnoDB. This also corresponds with the downloadable scripts file, sql.sql, with all the SQL commands from the book, which also specifies the InnoDB engine type when creating the forums table within the forum database. No new forum database or forums table is created between that point and the Chapter 7 review, and no update commands are given to change that table's storage engine between those points, so I'm lost.

Share this post


Link to post
Share on other sites

Sorry about the confusion. Before I make things worse, I'll double back and revisit the chapter and make sure I knew what I was thinking before I say more.

Share this post


Link to post
Share on other sites

This is my take on the question and maybe Larry will come back and tell me how wrong I am. But the forum database contains more than just the forums table, it also contains the messages table which is defined as MyISAM. The question reads why doesn't the forum database support transactions? I would answer because the database itself contains the messages table with the engine MyISAM.

 

Just my two cents.

Share this post


Link to post
Share on other sites

So, unless every table in the database is InnoDB, you can't perform transactions? That would mesh with the banking database example where all the tables use the InnoDB storage engine, in which case my only complaint would be that that case is not explicitly stated anywhere in the text and, for that matter, anywhere in the MySQL documentation on transactions. Seems like with regards to the forum database, transactions should be usable (though maybe not entirely useful) for queries dealing the users table and forums table, just not the messages table.

Share this post


Link to post
Share on other sites

You can mix MyISAM tables with InnoDB tables in the same DB. The following might shed some more light on this:

http://stackoverflow.com/questions/1970242/can-i-use-innodb-and-myisam-tables-in-one-database

 

Also, here's a nice, simple explanation of the pros and cons of MyISAM and InnoDB:

http://stackoverflow.com/questions/2114505/myisam-and-innodb-tables-in-one-database

  • Upvote 1

Share this post


Link to post
Share on other sites

Center, Your first question was something I was trying to figure out as well so I just tried it. I started a transaction against the forum database and I updated a row in the messages table and then a row in the users table. I then did a rollback and got the following warning:

 

 

0 row(s) affected, 1 warning(s):

1196 Some non-transactional changed tables couldn't be rolled back

 

I checked the tables and my change to the message table wasn't rolled back but my change to the users table was. So then I did the same test except I didn't update the messages table and when I did the rollback I got no warning message and the users table was rolled back.

 

So from my very basic testing I would say if you wanted to you could use transactions against the forum database as long as you didn't perform any updates against the messages table expecting to be able to do a rollback.

 

But back to the original question, the forum database would not support transactions because of the messages table being MyISAM. Although it looks like MySQL will not stop you from trying. :-)

  • Upvote 1

Share this post


Link to post
Share on other sites

@HartleySan, thanks for the very useful info.

 

@Dan, thanks very much for clearing that up. Your take on it seems to more than reasonably answer the review question in the context given between the two chapters. And thanks for trying it out yourself and posting the results, I hadn't gotten time to do that yet.

 

It's better if I didn't send Larry into a panic about updating the errata page... :unsure:

Share this post


Link to post
Share on other sites

Nah, nothing wrong with asking a question when something isn't clear.

Honestly, I haven't taken the time to look back through the chapter and try to answer the question myself yet, so perhaps Larry can still provide some insights.

Share this post


Link to post
Share on other sites

Before I actually say anything here, can someone clarify what, exactly, I'm answering or providing insights on? Thanks!

Share this post


Link to post
Share on other sites

Hi Larry,

 

We are all refering to the comment you made back on 9/26 in regards to the question centerNegative had asked regarding the forums database. HartleySan and I tried our best to figure it out and provide assistance, but I think we were also waiting to hear back from you to confirm that we were right on or way out in left field with our response.

 

Dan

Share this post


Link to post
Share on other sites

Sorry for the delay and for the confusion. In Chapter 6, the forum tables are created without mentioning the storage engine, that is true. But in Chapter 7, an ALTER command is used to convert those tables to the MyISAM storage engine (in order to use FULLTEXT searches). So you can't use transactions on those tables because they're aren't the InnoDB type.

  • Upvote 2

Share this post


Link to post
Share on other sites

I finally got around to chapter 10 Review and Pursue section after getting VERY sidetracked with chapter 9 Review and Pursue, banking database. I haven't added any error checking just to keep it simple.

 

In order to get the edit.php and delete_user.php to display the user name in the browser title bar (and also the header because it looks good) I found that I had to change some lines in the view_user.php script. Lines 76-86 (page 324) I replaced with:

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
extract($row);/*this allows variables with the same name as the $row['something'] to be created.
				    e.g $row['something'] becomes $something
				    I came across this and thought it a neat trick */
$bg = ($bg == '#CCC' ? '#FFF' : '#CCC');

echo'<tr bgcolor="' . $bg . '">
<td align="left"><a href="edit_user.php?id=' . $user_id . '&fn=' . $first_name . '&ln=' . $last_name . '">Edit</a></td>
<td align="left"><a href="delete_user.php?id=' . $user_id . '&fn=' . $first_name . '&ln=' . $last_name . '">Delete</a></td>
<td align="left">' . $user_id . '</td>
<td align="left">' . $last_name . '</td>
<td align="left">' . $first_name . '</td>
<td align="left">' . $dr . '</td>
</tr>
';
}//end of while loop

 

In the delete_user.php script, I added:

if ($_SERVER['REQUEST_METHOD']=='GET'){
$page_title= 'Delete User: '. $_GET['fn'] . ' ' . $_GET['ln'];
}else{
$page_title="Delete User: " . $_POST['first_name'] . ' ' . $_POST['last_name'];
}
//and then the header and title as before
include ('includes/header.html');
echo '<h1>Delete a User</h1>';
//check for valid user ID, through GET or POST
if ((isset($_GET['id'])) && (is_numeric($_GET['id']))){//this page accessed from view_user.php
$id = $_GET['id'];
$fn = $_GET['fn'];//needs this to pass to form
$ln = $_GET['ln'];//needs this to pass to form
}elseif((isset($_POST['id'])) && (is_numeric($_POST['id']))){//submitted from this page and form
$id = $_POST['id'];
$fn = $_POST['first_name'];//from form from $_POST
$ln = $_POST['last_name'];//from form from $_POST
}else{//no valid ID. end script
echo '<p class="error">This page has been accessed in error.</p>';
include ('includes/footer.html');
exit();
}

 

I also made some additions to the <form> that starts at line 59 on page 305:

 

//create the form
echo '<form action="delete_user.php" method="post">
<input type="radio" name="sure" value="Yes" />Yes
<input type="radio" name="sure" value="No" checked="checked" />No
<input type="submit" name="submit" value="Submit" />
<input type="hidden" name="id" value =" ' . $id . '" />
<input type="hidden" name="first_name" value =" ' . $fn . '" />
<input type="hidden" name="last_name" value =" ' . $ln . '" />
</form>';

These last 2 inputs pass hidden values to $page_title.

 

In the edit_user.php script, I made the following changes:

//change page title to suit user to be edited as per Review and Pursue chapter 10: not secure: just to show principal
if ($_SERVER['REQUEST_METHOD']=='GET'){
$page_title= 'Edit User: '. $_GET['fn'] . ' ' . $_GET['ln'];
}else{
$page_title='Edit User: ' . $_POST['first_name'] . ' ' . $_POST['last_name'];
}
//and then the header and title
include ('includes/header.html');
echo '<h1> Edit User Details</h1>';

I also added the following lines to the UPDATE query that starts on line 56. This displays a message if nothing has been altered and prevents an error message.

}elseif(@mysqli_affected_rows($dbc)==0){//no row changed: no new details
//display message
echo '<p>No new details have been inserted.</p>';

 

Hope this lot helps someone.

 

For the 2nd Pursue question, I am creating a version of edit_users.php that allows the changing of a user's password which is a cross between the change_password script and the edit_user script. My take on this is more complex than the suggestion on one of the sidebars in chapter 10, page 315. I have hesitated to publish it in the forum as it currently runs to 145 lines of code. Once I add the error/security checking it will run to rather more. I therefore intend to display it on a page on my site at www.visitingfife.co.uk as soon as I am happy with it.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...