Database insertion / retrieval : best practices

[drewbee]

Well, now it is time to really start cracking down on my programming and standards way of doing these. This post is in regards to escaping strings, insertion, retrieval, and slashes.

 

As it stands right now, my current setup is the following.

 

Before querying my code goes through these basic checks (and more precise if needed, but for basic general data):

 

- is data required minimum length

- is data not longer then required maximum length

- is data right type (int, string, boolean etc)

 

Once again this is just general data, with nothing in particular. If it was an email address i would validate, or a phone number it would be a step further in validation etc.

 

At this point in time it will be inserted, surrounded by mysql_real_escape_string.

 

If it makes it through the checks above, I make the assumption that whatever the user put in IS what it is and nothing more. If they say that the state they are from is <? echo str_replace("",$string); ?> then it goes into the database like that.

 

 

The only really notable problem that may occur from above is our little friend magic_quotes. Mine is turned on of course. Any quote that goes in gets escaped with a slash. One issue this causes is if you are expecting a field length to be 5 characters, and one of those characters is a quote, we now have a 6 character string (5 characters + newly added slash to the quote). However, I don't like the idea of stripping the slashes before inserting.

 

When it comes back out, it gets the htmlspecialchars(stripslashes($row['myvar'])) treatment.

 

At this point in time any html, or any other sudo random crap they tried to insert and inject will be removed. (or more so replaced with its respective char). I dont want to try and manipulate data that they entered. That is a no no. Rather, redisplay in non-harmful  but attempted to be harmful text that they entered (which as far as I am concerned works perfectly).

 

 

Any thoughts, ideas, or suggestions on this process?

 

What do you guys do for these three functions and in what order?

 

Thanks for the insight,

Drew Dulgar

[teng84]

suggestion when asking a question please do it one by one its hard to find somebody to answer this kind of q very long what is this a novel LOL

[dbo]

Sounds to me that you're not so much asking a question as you are looking for confirmation that you're doing the right things... and for the most part you're doing the right stuff. If you have any particular questions or just want to hear me rant about the topic let me know.

[drewbee]

suggestion when asking a question please do it one by one its hard to find somebody to answer this kind of q very long what is this a novel LOL

 

Right, I know! It was lengthy though and required that type of description.

 

Sounds to me that you're not so much asking a question as you are looking for confirmation that you're doing the right things... and for the most part you're doing the right stuff. If you have any particular questions or just want to hear me rant about the topic let me know.

 

I guess that could be said. Confirmation, or a more "proper" way of doing it. Basically I want the best way to protect my systems while allowing the user free reign. I always enjoy a good rant so have at it if you want

[dbo]

My rant was invited.... so hang onto your seats!

 

[rant]

So I got to thinking about the role of us technologists. Typically we are creating technology to fill some sort of need, drive out some efficiency in a process or even automation. That's cool stuff and makes a lot of sense. We want to make things easy! So why the hell don't we do the same for ourselves? Why don't we look for efficiencies in how we do development, frameworks/readable/maintainable code. Some of us do of course... but the vast majority of us could and should do it better. So then I got to thinking about a flavor of the 80/20 rule... if 80% of my time is spent in 20% of the actual development, then that's where I should be focusing my efforts at least initially. And interestingly enough when you do things "right" you spend a huge amount of time doing input/user validation.

 

So what are the goals here?

-Validate user input on the client side... so we cut down on extra calls to the server and it's less annoying for a user to have to click and wait.

-Validate user input on the server side... hopefully it's just a double check... but we can't trust that javascript did it.

    -I sure as hell don't want to right the same code twice! Or worse yet... maintain two sets of validation rules, that's a maintenance nightmare!

-Error messaging... in the event that we do encounter errors what's a robust way to present those back to the users?

-Prefilling the form with data that was valid so that the user doesn't have to input the same stuff over and over

 

So all of that stuff sounds pretty basic right? Pretty reasonable requirements for most data driven sites, yet pulling off that takes a ton of work. And why should we write the same code? These same requirements come up regardless if you're building an ecommerce site... some sort of sports statistics... or even a financial reporting application. Really sounds to me like there is a better solution.

 

You can view my other post and snipet of code titled "Input Validation" on how I'm attempting to pull all of this off, but essentially it entails storing the validation rules in an xml structure so that the same rules can be utilized for both PHP and javascript. I then load in the xml... write a parser one time and use it for all input validation. The result is an object that contains whether or not the field test passed or failed and an array of all appropriate error messages. I accomplished this in 4-5 lines of code for validating an entire form! This is a huge time saver considering a recent application I developed consisted of 8-10 forms with 60-80 fields each.

 

Ok so, make your own conclusions about that piece. We've now got valid data. Essential PHP Security (by Chris Shifflet) is a good read. Anyways it suggests storing filtered input in an array $clean so that you can clearly see that the data has been appropriately filtered... any data not in the $clean array... is well... dirty!

 

So given your clean data you need to then make sure that it first of all does not contain any thing that might break an sql statement or allow for sql injection. So you run a routine over your $clean array which effectively escapes it. But what about code injection? A quick & dirty way to do this is to just replace < with ( and > with )... but this isn't always acceptable. 

 

So anyways we've now verified that our input follows our rules by doing client and server side validation, we've stripped potential malicious code and we've escaped the string for the database. We can now pretty safely say that our data can be inserted... go for it.

 

But waaaaaaaait. Don't just use the native mysql functions or whatever database you are using. Wrapper this crap inside of a class and then call those functions. The reason for this is that if mysql changes to mysqli you shouldn't need to change 15,000 occurences in 400 different files to update your site. You should modify one file.... you're "wrapper" class and the changes take place. If you do a really good job writing good SQL queries you could even change the underlying database from MySQL to say Oracle and the whole site work just by modifying one file. So "wrapper" that crap. Don't write the same code twice!

 

So anyways we've inserted data...  on the way out you want to use htmlentities or some equivalent to convert special characters to a format that will make it displayable in the pages... we don't want to actually change this in the database because we could potentially want to use our data in some source other than the web.

[/rant]

[drewbee]

Thanks dbo, a good read even when ranting!!

 

I do agree that there are the circumstances that involve basic, routine cleaning (I actually use an object for this). As well, there are the more specific tasks of validation (phone, email etc).

 

 

I actually have a templating class that is inherited by every instance of every page that i want to include, and this templating involves the use of validation techniques as well. Because it is object oriented, it can be used, manipulated, and changed in any instance that I want.

 

my basic work flow on the actual php page that is brought up on the screen ie.

 

mypage.php looks like this:

<?

$page = new Page();

 

if ($page->isPostBack)

{

     $page->validateForm();

     if ($page->ERRORS)

     {

         $page->throwWarning('Error title', $page->ERRORS);

         $page->drawForm('form_name');

     }

     else

     {

          $page->Process();

     }

}

else

{

    $page->drawForm('form_name');

}

 

 

$page->drawHTML();

?>

 

The thing that is fun about this is that nothing is drawn (no html output) to the screen at all until drawHTML is called, so in my process() function, I have full access to setting/changing headers, including redirects and the like.

 

If an error is detected it will use the function throwWarning() which will display a nice little red box with all of the error messages above the form that the error occured at.

 

I also have another function called throwError which will actually remove any content and only display the error message, draws the template, then followed by a die() to prevent any further processing. This makes me be able to throw full out error messages, while still displaying it nicely with my template.

 

I actually use the throwError() function with the use of http error messages (400, 500 etc).

 

In your rant you talk about not having to hit the back button. The way this is setup it redisplays the form with the information they sent from the previous screen ,as well as any error messages. They do not need to hit "back" to go back to the form. While they do "move forward", all the information and error messages is redisplayed to them.

 

Thanks for the rant again!

[dbo]

Looks like you're doing a lot of good stuff.

 

In your example how are you loading up the HTML? Are you pulling it from a database?

[drewbee]

No, they are from html files.

 

Every one of these that use a form (or any type of input) have a minimum of:

 

1. if current_page == filename die()  - prevents users from opening up the actual file if the location is actually discovered. Though even if this did happen the form would just submit to its counter part.

 

2. If it is editing an already submitted it will populate the form vars from the database.

 

3. all form variable constructors are initialized here right before the display of the form. I'm one of those picky php programmers that programs with E^ALL for warnings set.

 

4. html for form.

 

I basically have a forms directory with all of the corresponding files, by passing the value to drawForm finds and form i want by name comparisons. (it either exists or it doesn't).

 

5. All dynamic data (regardless of where it came from db, user input etc) inputs are htmlspecialchars() and stripslashes()

[dbo]

Interesting... I wouldn't mind seeing some of your code for this stuff if you're willing of course.

[drewbee]

It is strange actually, because it seems like it is alot of "overhaul" to do this, but because of the way I am setup, I can create a fully functioning add form / edit form / view form in about 30 minutes, with complete security protection.

 

Because I am consistent with what I do, and I do it that exact way everytime my development is fast, rapid, and can be easily changed as well. If I need to make a change to the form to take new values, or drop values it takes 10 minutes at the absolute most.

 

for changing a form all I require is this:

 

- open class for what the particular form resides in

  - edit process and validate functions

- open the actual html form

    - edit form

- edit database for change

 

And because of this as well, it is an all or nothing type of situation. If one thing is out of sync it all fails. It helps me guarantee everything is correct.

 

Fun stuff, I think

[drewbee]

Here is my page.class.php

 

 

 

<?
<?
class Page
{
var $sessionID;
var $user = array();

var $currentPage;

var $leftColumn = array();
var $centerColumn = array();
var $menu = array();

var $drawAccountBox = true;

var $isPostBack = false;

var $pageTitle = 'Freelance';

var $wysiwyg = false;

function Page($loginRequired = false)
{

	mysql_connect('localhost', 'asdf', 'asdf');
	mysql_select_db('adf');

	session_start();
	$this->sessionID = session_id();

                // I use request URI because of MOD REWRITE
	$this->currentPage = $_SERVER['REQUEST_URI'];

	$validateLogin = mysql_query("SELECT session validation stuff");


	if ($user = mysql_fetch_assoc($validateLogin))
	{
		if ($user['AccountStatus'] == '0')
		{
			$this->throwError('Your Account Has Been Banned');
		}
		else
		{
			$this->user = $user;
		} 
		$update = mysql_query("UPDATE session vals with last movement");
	}
	elseif (mysql_num_rows($validateLogin) == 0)
	{
		$createSession = mysql_query("Create new session in table");
	}
	else
	{
		$this->throwError('A problem has occured with the login system');
	}

	// set isPostBack
	$this->isPostBack = count($_POST) > 0 ? true : false;


	$this->AddColumn("Account",ob_get_contents(),"left");
	ob_end_clean();

	// Add Default Menu
	$this->menu['/index.html'] = 'Home';
	$this->menu['/register.html'] = 'Sign Up';
	$this->menu['/cp/index.html'] = 'Control Panel';

	if ($this->user['AccountID'] == 0)
	{
		// Login

		if ($this->isPostBack && isset($_POST['login']))
		{
			$this->Login();
		}

	}
	else
	{

		if (isset($_GET['logout']))
		{
			$this->Logout();
		}

	}

	if ($loginRequired)
	{
		if ($this->user['AccountID'] == 0)
		{
			$this->throwError('Login is required to access this page');
		}
	}

}

function Login()
{

	$validateUser = mysql_query("SELECT User From table");

	if ($row = mysql_fetch_assoc($validateUser))
	{
		// Correct Username
		$_SESSION['LastLogin'] = $row['LastLogin'];

		// Update The session Account ID / last Login
		$session =	mysql_query("Update session accountid / last login"); 


		if (!$session || !$account)
		{
			$this->throwError('A problem has occured involving the login system.');
		}
		else
		{
			if (isset($_GET['logout']) || $_SERVER['PHP_SELF'] == '/register.php')
			{
				header("Location: /index.html");
			}
			else
			{
				header("Location: {$this->currentPage}");
			}
		}
	}
	else
	{
		$this->throwError('An invalid username or password combination was used');
	}	

}

function Logout()
{
	$logout = mysql_query("log user out of table");

	$_SESSION = array();

	if (mysql_affected_rows() == 0)
	{
		$this->throwError('Their was an error logging you out.');
	}
	else
	{
		$this->centerColumn = array();
		$this->AddColumn('Logout', 'You have been sucesfully logged out');
	}	
}

function throwError($message)
{
	// Overwrite the center column with one item and close out of the page.
	$this->centerColumn = array();
	$this->AddColumn('The following errors occured', $message, 'center', true);
	$this->drawHTML();
	die(); 

}

function throwWarning($title = "The following information is incorrect", $message)
{
	ob_start();
	echo "<ul>\n";
	if (is_array($message))
	{
		foreach ($message AS $notice)
		{
			echo "<li>{$notice}</li>\n";
		}
	}
	else
	{
		echo "<li>{$message}</li>";
	}
	echo "</ul>\n";

	$this->AddColumn($title, ob_get_contents(), 'center', true);
	ob_end_clean();
}

function AddColumn($title, $content, $location = "center", $error = false)
{

	switch($location)
	{
		case "left":
			$this->leftColumn[] = array('title' => $title,
										'content' => $content,
										'error' => $error);
		break;
		default:
			$this->centerColumn[] = array('title' => $title,
										  'content' => $content,
										  'error' => $error);
		break;

	}

}


}
?>