Redundant Data Class in PHP {Theory}

by: Caleb Jonasson - May 30th, 2011   Filed under: php, Web Development 2 Comments

As a server side programmer you are going to be spending a lot of time working with data that is pulled from the database and in order to make sure that you do not have any loose ends it is a good idea to handle this situation with a class. This article is not going to cover how to code a data class nor is it going to help you create a specific data class. This article is here to show you how to make your data redundant and error proof.

Pulling all of your information

When pulling the information off the get go you are going to want to do one thing and one thing only: query all of the data that you can use in the class. The query is going to take place inside of a function and will most likely be used every time that you are in need of the data in the database. Once the query to obtain this information has taken place then you are going to either have results or no results and the easy way to check is simply by checking the number of rows returned.

Say we were trying to access user information for a user page. It would be a good idea to query and make sure that all of the information is available. In this query we are most likely going to be joining up to multiple tables because having all of the user information stored in one table would create a fair amount of overhead and would increase the time it took to go through the table. For this reason we break things apart.

An Example Of Database Tables

A well structured database table is going to be pulling from an auto incrementing ID. This auto incrementing id is going to be found on every table but since we can not just assume that if we insert a record into tbl_user and tbl_user_profile at the same time we are going to get ID’s that match properly. Because this is a flawed way of looking at the table set up we are going to use the auto incremental ID from the tbl_user and plug this into a new column on the other user tables. This way we can simply join the tbl_user.user_id to tbl_user_profile.user_nid. Now that we have a relationship in the database that will work and is pretty redundant in theory we are still going to have to deal with the situation of a table not being created properly upon user registration and thus we are lead to…

Enforcing Existing Tables

Remember back up at the top when I was talking about the query all function either working or not working? Well this function upon returning zero rows will tell us one of two things. (Assuming the SQL was written properly.) The first thing that it will tell us is that this user does not exist, and the second thing is that the user is missing a table. This could mean that something was deleted, or maybe the table was recovered in backup at a later date and there was a record missing. Either way we know that by checking the user table for the users existence we are able to confirm that we are dealing with missing data and this is where the checking functions come into place.

In a well written data handling class there will be functions that exist to pull results from each of the individual tables. These functions are excellent for checking that a user does exist in the following table and are a great way to quickly and easily pull sections of content based on the user which are handy when loading content on an interval via AJAX. But now we are getting a little off topic. Creating a function that can pull information and return a number as a status and creating another function that will insert defaults into the table is an excellent way to make sure that you do not lose data and that all of the data is being pulled properly.

Recap

When using the class you are most likely just going to need to get the information which means that you will be using a function that behaves like the queryAll that I was talking about. If the user does not exist then you can simply return false.

The next step is to create functions that will check individual tables based on that original tbl_user.user_id (or whatever yours is) starting with the initial tbl_user which the rest of the tables are based on. This will tell you if it was created in the first place or not.

If we have gotten far enough to know that the user does exist and the user simply doesn’t have a record in one of the tables then we can query each of the tables and find out where the record is missing. Now that we know the table we can simply insert blank data into the table and maybe send a notification to the user that they may need to update a certain part of their profile.

Knowing when we have an error in table creation

Through this data class it is just a matter of adding in an error log message to let the administrators tell if the application has a bug in it but this shouldn’t be a problem if you are handling proper inserts and updates through SQL and checking for an affected row upon creation.

Tags: , , ,

cURL 403 Error Returning

by: Caleb Jonasson - May 26th, 2011   Filed under: php, Web Development Add Comment

The other day at work we ran into an issue where the server would return a 403 error page when retrieving page information from a cURL call. After searching around the web for a while thinking that we had a server permission issue on our hands it ended up just being a PHP problem.

In order to make a cURL request from your own server you must first make sure that the session has been destroyed prior to and cURL commands. This is because your server cannot have two pages that can access sessions up at the same time and the primary file that you are working from is going to lock the secondary file that you are trying to bring in.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?php
session_start();
 
//authentication code.
 
//destroy session first.
session_destroy();
 
//cURL code.
$ch_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $fields_string);
$response = curl_exec($ch)
curl_close($ch);
 
//now we can start the session again
session_start();
 
//do some other stuff...

My only guess as to why PHP does this is to protect itself from breaking sessions with multiple page access which is a pretty good security issue to have in place. Better error reporting would have been nice though.

Tags: , , ,

PHP: Array Of Bad Words

by: Caleb Jonasson - May 20th, 2011   Filed under: php, Web Development 1 Comment

When creating applications that are going to be used by hundreds of thousands; it is important to make sure that you have the proper facilities in place to handle curse words that are entered in by users. This can be done by checking an array of bad words.

The code is simply…

1
2
3
4
5

<?php
//foul language array
$this->badWords = array('word1', 'word2','word3');
//Now you just need to go through your string and make comparisons.
?>

Rather then posting the code directly onto the blog I would rather have a site that is safe for all readers and not be indexed with foul language and racial slurs and thus is why I am offering the array via a text file within a compressed zip.

Tags: , , ,

JavaScript: setTimeout Firing Immediately

by: Caleb Jonasson - May 19th, 2011   Filed under: javascript, Web Development Add Comment

Today at work I needed to do some Ajax calls to update some information in the database and pull the most recent results for the administration side of the project. I was stumped for about 10 minutes while trying to figure out why my button would not change back to its original state after displaying “updated.” It turns out that when you call a function using ‘setTimeout’ and you do not wrap the function in quotation marks, the function will be triggered immediately.

1
2
3
4
5

//function is triggered immediately.
setTimeout(updateRecord(), 500);
 
//function is triggered after the set time.
setTimeout("updateRecord()", 500);

This immediate triggering makes sense when you think about how the triggering within the parameters operates but it got me all the same.

Tags: , ,

.htaccess Handling Bad Server Requests

by: Caleb Jonasson - May 3rd, 2011   Filed under: htaccess, Web Development Add Comment

The plain old and common 404 error can get a little old and for that reason I am going to fill you in on how to handle bad status reports that you can easily handle. In order to follow along you are going to need access to your .htaccess file along with a normal way to handle URLs without special mapping.

The Code

In order to make this work successfully we are first going to need to open up the .htaccess file. If you do not currently have one but know that your host supports the functionality then you can just open up notepad and save the file as “.htaccess”. With the file in place we are going to need to add the following lines of code to make the server check for proper results.

1
2

#force error document handling.
ErrorDocument 404 /error/404.php

As you can see in the above code we have a comment followed by an error document code. We have set the status type that we are after to 404 and then pulled the page from the directory “error” and requested the file named 404.php.

When requesting a page like this you are not actually redirecting the user to the new page but rather you are bringing in the 404.php to be displayed. This allows the user to keep the attempted url in the window in case they need to make a change to the spelling.

Adding More

If you would like to handle things such as 401 requests as well you can do the same thing on the next line below. It will allow you to handle a different error with the same page or you can always change up the page to display something else. This allows for unique page error messages.

1
2
3

ErrorDocument 401 /error/404.php
ErrorDocument 404 /error/404.php
ErrorDocument 500 /error/500.php

Why It Won’t Work With URL Mapping

When working with URL mapping in a way that allows you to build the application from the index file you shouldn’t have to deal with handling status’ at all. This is because you handle all of the fake directories and if a directory doesn’t match up to a predefined one then you can simply just throw it to a 404 page if you desire.

Tags: , ,

Password Protecting Your Directory With .htaccess & .htpasswd

by: Caleb Jonasson - April 12th, 2011   Filed under: htaccess, Web Development Add Comment

Introduction

There are many ways to password protect a website and the simplest way to do this, even though not very secure, is by using an ht password.  This is a quick solution for password protecting a website that is currently under development. The idea behind this prevention is to cut off all directory access unless the user has entered in the proper username and password combination.

Note: When connecting to the server and using data services you need to make sure that the directory that the data service uses is not protected. Applications such as Flex don’t handle this form of protection very well.

Requirements

In order to get this to work you are going to need two files on the server. The first is an .htaccess file and the second is a .htpasswd file. The first file (.htaccess) is going to hold the location, information saying that it is a pass worded directory, and the location of the .htpasswd file.

1
2
3
4

AuthUserFile /full/path/to/.htpasswd
AuthType Basic
AuthName "root"
Require valid-user

What the Code Means

The first line of this file is the direct location to the htpasswd file from the htaccess file. The second line is the authentication type, simple is the most common type used, and requires that the browser is able to handle a 401 request which prompts for username/password insertion. The third line is just a name for the authentication that is taking place. You can just call this root. The final line is the one that is triggering the password requirement.

The .htpasswd File

The .htpasswd file should look a little different. It is going to just store the username and the password connection. Note that the password is being hashed and this requires that you generate the hash for it. If you would like to generate it yourself you can do so using php or apache, however, you can also just click on this link to find a website that will generate it for you. http://www.htaccesstools.com/htpasswd-generator/

1

caleb:28khj%js@as487dga
Tags: , , , ,

Getting Secured Using Mysqli

by: Caleb Jonasson - April 9th, 2011   Filed under: php, SQL, Web Development 1 Comment

Introduction

The Mysqli class is a great help to working towards proper security when sending and pulling information from the database. It can stop injection attacks in their tracks and will allow you to specify data types allowing you to not let anything bad slip into the system.

In this article I will be going through the step by step approach to creating a query using prepared statements through MySQL. We are going to follow best practices and use the object oriented style to achieve a connection and gather our results.

Connecting to the database

The first thing that we will be doing is connecting to the database. Traditionally this should be done using an outside class to prevent an access to the connection information. The connection string will attempt to connect to the database and open up a persistent connection.

1

$this->connection = new mysqli('localhost','username','password','database');

Creating the prepared statement

The next thing that is required is for us to store the prepared statement. This should be attempted inside of an if statement. This way we can handle any errors appropriately.

1
2
3
4
5
6
7
8
9
10

$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
 
}else{
	die(trigger_error($this->connection->error));
}

Wild cards

You probably noticed that the sql code has something new inside of it. This is the wild card for the sql statement and a ‘?’ is used to display it. This will allow us to bind variables in place of these questions marks and specify their data type.

Bind parameters

Bind parameters is the next step that takes place when there are wild cards inside of the sql statement. These allow us to force a data type into the format of our choosing. There are 4 data types that we can use.

‘I’ Integer
‘s’ String
‘d’ Decimal
‘b’ Blob

1
2
3
4
5
6
7
8
9
10
11

$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
	//Now bind the data to the types.
	$stmt->bind_param('sii', $bookName, $bookYear, $limit);
}else{
	die(trigger_error($this->connection->error));
}

The above function that we just used will take a minimum of two arguments, the first to define the variable types and everything after that is a variable. Notice that all of the type declarations are in the same string. A common error for this piece of code is that you have an invalid amount of arguments being passed. Another error commonly given is that you cannot have data placed directly in the string. You need to reference variables.

1
2

//This is not allowed:
$stmt->bind_param('sii',"the php book", 4, 20);

Execution

Once we have properly set up our bindings we can execute the prepared statement and try to get a result from the database. As you can see in the code below; this can fail and an error message will be stored in case it does. This is why we are going to have it placed inside of an if statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
	//Now bind the data to the types.
	$stmt->bind_param('sii', $bookName, $bookYear, $limit);
	//We will now execute the statement. This will throw an error if unsuccessful.
	if($stmt->execute())
	{
 
	}else{
		die(trigger_error($stmt->error));
	}
}else{
	die(trigger_error($this->connection->error));
}

Number of Rows

The next step in our quest for world domination is to store the results inside of the statement variable. This is going to let us make a call for the number of rows so we can handle the case of no rows being returned.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
	//Now bind the data to the types.
	$stmt->bind_param('sii', $bookName, $bookYear, $limit);
	//We will now execute the statement. This will throw an error if unsuccessful.
	if($stmt->execute())
	{
		//check the number of rows.
		$stmt->store_result();
		if($stmt->num_rows >= 1)
		{
 
		}else{
			echo 'Nothing was returned from the database.';
		}
	}else{
		die(trigger_error($stmt->error));
	}
}else{
	die(trigger_error($this->connection->error));
}
<pre>
<h2>Binding Results to Variables</h2>
The last thing that is required is binding the results and looping through the results. As you can see we are actually creating variables with this function and storing the information into them. The variables created are gathered from the select part of the statement. This means that we need to define the variables in the same order as the sql statement.
<pre lang="PHP" line="1">
$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
	//Now bind the data to the types.
	$stmt->bind_param('sii', $bookName, $bookYear, $limit);
	//We will now execute the statement. This will throw an error if unsuccessful.
	if($stmt->execute())
	{
		//check the number of rows.
		$stmt->store_result();
		if($stmt->num_rows >= 1)
		{
			//bind the results
			$stmt->bind_result($id, $name, $year);
			//loop through the results.
			while($stmt->fetch())
			{
				echo "$name: $year<br>";
			}
		}else{
			echo 'Nothing was returned from the database.';
		}
	}else{
		die(trigger_error($stmt->error));
	}
}else{
	die(trigger_error($this->connection->error));
}

It is now time to run your code and gather the results using the fetch function. If you only have to return one row from the database then you can just use the fetch function outside of the while loop and it will allow you to pull the results and use the variables in the same way you would in the while loop.

Tags: , , ,