Help with starting off with PEAR MDB2!!!

[wwwfilmfilercom]

Ok well I have tried to read up on the documentation at http://pear.php.net/manual/en/package.database.mdb2 and it's not very useful to someone who only started working in PHP/MySQL about 2 weeks ago!!!

I just need someone to start me off with an example so I can compare and see how much of my code needs to be changed etc....

Here's an example of a page:

<?php 
ob_start();
require( "header.php" );
?>

<?php
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

session_start();   
$_SESSION['redirect'] = "addpost.php";
require_once("auth.php");

// check levelid is ok for this page
if ($db_level<1) // user doesnt have access
{
die(header("Location: notauth.php"));
}

?>

<h3>Add post!</h3>
<p>Use the form below</p>
<br />

<?php
include("config.php");

$userinfo = mysql_real_escape_string($_COOKIE['my_username']);

$check = mysql_query("SELECT Userid FROM Users WHERE Username = '$userinfo'")or die(header("Location: notloggedin.php"));
$userid=mysql_result($check,$i,"Userid");
if ($userid < 1) {
die("Location: notloggedin.php");
} 
?>

<form name="addpostform" action="postme.php" method="post" onsubmit="return formCheck(this);">
User id: <? echo "$userid"; ?><input type="hidden" name="userid" value="<? echo "$userid"; ?>"><br />
Title: <input type="text" name="title">
<script>
displaylimit("document.addpostform.title","",50)
</script><br /><br />
Category: <select name="category">
<?php

$query="SELECT * FROM Categories";
$result=mysql_query($query) or die( "Unable to do query");

$num=mysql_num_rows($result);

if ($num==0) {
echo "The database contains no categories";
} else {

$i=0;
while ($i < $num) {

$catid=htmlentities(mysql_result($result,$i,"Catid"));
$catname=htmlentities(mysql_result($result, $i, "Catname"));
echo "<option value='$catid'>$catname</option>";

$i++;
}
}

?>
</select>
<br />
Info: <textarea rows="10" cols="40" name="info"></textarea>
<script>
displaylimit("document.addpostform.info","",500)
</script><br />
Location: <input type="text" name="location">
<script>
displaylimit("document.addpostform.location","",50)
</script><br /><br />
Requirements:<br />
<?php

$query="SELECT * FROM Skills";
$result=mysql_query($query) or die( "Unable to do query");

$num=mysql_num_rows($result);

if ($num==0) {
echo "The database contains no skills";
} else {

$i=0;
while ($i < $num) {

$skillid=htmlentities(mysql_result($result,$i,"Skillid"));
$skillname=htmlentities(mysql_result($result,$i,"Skillname"));

echo "<input name='skills[]' type='checkbox' value='$skillid'>$skillname: <textarea wrap=virtual rows='3' cols='30' name='txt[$skillid]'></textarea><br />
(150 characters max)<br />";

$i++;
}

mysql_close();

}

?>
<br />
<input type="Submit" value="Post">
</form>

<?php
require( "footer.php" ); 
ob_end_flush();
?>

Now I am sure there are many things in this code which can be done better - can you please point these out so I can get to fixing ALL of my pages! Mainly I need to know how to begin to start converting this for use with PEAR::MDB2.....

Any ideas: I'm feelin like this at the moment , and it was all going so well ..lol

[wwwfilmfilercom]

Dam - what to do if you're not that great at MDB2 etc... what alternatives are there because I'm finding this quite complicated, especially to modify the code. I think it would be much easier to do from scratch (will I definitely WILL be doing)...

Just thinking at this stage I will use PHP to validate the code and beta taste as far as I can - how is that for an idea??

[visualAd]

Dam - what to do if you're not that great at MDB2 etc... what alternatives are there because I'm finding this quite complicated, especially to modify the code. I think it would be much easier to do from scratch (will I definitely WILL be doing)...

Just thinking at this stage I will use PHP to validate the code and beta taste as far as I can - how is that for an idea??

When you indent you code properly I'll help you

[wwwfilmfilercom]

Lol I hope you do VisualAd I'm workin on it right away!!!!

[wwwfilmfilercom]

<?php 
ob_start();
require( "header.php" );

include("config.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

session_start();   
$_SESSION['redirect'] = "addpost.php";
require_once("auth.php");

// check levelid is ok for this page
if ($db_level<1) // user doesnt have access
{
	die(header("Location: notauth.php"));
}

?>

<h3>Add post!</h3>
<p>Use the form below</p>
<br />

<?php

// MAKE SURE USER LOGGED IN
$userinfo = mysql_real_escape_string($_COOKIE['my_username']);
$hash = mysql_real_escape_string($_COOKIE['my_hash']);

$check = mysql_query("SELECT Userid FROM Users WHERE Username = '$userinfo' && Userhash='$hash'")or die(header("Location: notloggedin.php"));
$userid=mysql_result($check,$i,"Userid");
if ($userid < 1) {
	die("Location: notloggedin.php");
} 
?>

<form name="addpostform" action="postme.php" method="post" onsubmit="return formCheck(this);">
User id: <?php echo "$userid"; ?><input type="hidden" name="userid" value="<?php echo "$userid"; ?>"><br />
Title: <input type="text" name="title">
<script>
displaylimit("document.addpostform.title","",50)
</script><br /><br />
Category: <select name="category">
<?php

$query="SELECT * FROM Categories";
$result=mysql_query($query) or die( "Unable to do query");
$num=mysql_num_rows($result);

if ($num==0) {
	echo "The database contains no categories";
} else {

	$i=0;

	while ($i < $num) {

	$catid=mysql_result($result,$i,"Catid");
	$catname=htmlentities(mysql_result($result, $i, "Catname"));
	echo "<option value='$catid'>$catname</option>";

	$i++;
	}
}

?>

</select>
<br />
Info: <textarea rows="10" cols="40" name="info"></textarea>
<script>
displaylimit("document.addpostform.info","",500)
</script><br />
Location: <input type="text" name="location">
<script>
displaylimit("document.addpostform.location","",50)
</script><br /><br />
Requirements:<br />
<?php

$query="SELECT * FROM Skills";
$result=mysql_query($query) or die( "Unable to do query");

$num=mysql_num_rows($result);

if ($num==0) {
	echo "The database contains no skills";
} else {

	$i=0;
	
	while ($i < $num) {

		$skillid=mysql_result($result,$i,"Skillid");
		$skillname=htmlentities(mysql_result($result,$i,"Skillname"));

		echo "<input name='skills[]' type='checkbox' value='$skillid'>$skillname: <textarea wrap=virtual rows='3' cols='30' name='txt[$skillid	]'></textarea><br />
(150 characters max)<br />";

	$i++;
	}

mysql_close();

}

?>
<br />
<input type="Submit" value="Post">
</form>

<?php
require( "footer.php" ); 
ob_end_flush();
?>

Ok I doubt this is as good as what you would add but seriously I NEED HELP with this - just an idea of what bits to change and how to change them, then I can carry on with the other pages....

Also if this doesn't look too good heres a better look: http://phpfi.com/181464


ThanksThanksThanks!

[visualAd]

Have a read of this: http://www.codepoets.co.uk/doc/php_p...b_applications

Pay special attention to the prepared statements bit. Oh, and documentation is for reference and you need a basic understand of the subject before it becomes useful.

[wwwfilmfilercom]

Thanks!!! Can you please just pass out any good links for this - I mean I searched online but the most popular weren't necessarily the best....

Anyway this looks good I'll take a look into it further. I'm actually thinking that I will start anew rather than try to modify old code.. Cheers

[visualAd]

Several easy steps and you are there:

/* 1) connect to the database */
$db =& DB::connect("mysql://user:password@host/database");

/* 2) prepare your query. use question marks to indicate where values form 
    PHP will be placed */
$preparedQuery = $db->prepare('SELECT * FROM user WHERE username = ? AND pass = ?');

/* 3) execute the query passing the parameters as an array
    these replace the question marks in the prepared statement above */
$result = $db->execute($preparedQuery, array('username', 'password'));

/* 4) read and print the results */
while(($row = $result->fetchRow())) {
    print_r($row);
} 

Code indenting should be a good habit that programmers learn from the outset. If you do not indent your code it makes it hard to read and hard to follow the logic.

[visualAd]

I mean I searched online but the most popular weren't necessarily the best....

Are you sure?

http://www.google.co.uk/search?q=mdb2+howto

Check the first link.

[wwwfilmfilercom]

Haha your waaaaaaay 2 smart for me.... thx for the tips about indenting, I do it in VB no idea why not here....

It actually seems quite straightforward but I think what was confusing me is how it will help with security - for instance I know I won't have to use mysql_real_escape_string or whatever it is, is that the same for htmlentities? How about other forms of validation? I found the Validate class and tried to install it using the package manager but as it is 'beta' I just got an error...

What do you use for validation/security - that is really why I am looking into PEAR and MDB2 (even though I know there are other advantages)?

[visualAd]

I'll show you what can happen. Lets take a poorly written script:

<?php
    require_once ('connection_injection.php');

    $username = @$_POST['username'];
    $password = @$_POST['password'];
    
    mysql_select_db('inject');
    $query = "SELECT * FROM user 
                    WHERE 
                username='$username' AND password='$password'";
    $result = mysql_query($query) or die (mysql_error());

    if (mysql_num_rows($result) > 0) {
        $userInfo = mysql_fetch_row($result);
    }
?>
<html>
    <head>
        <title>SQL injection Example</title>
    </head>
    <body>
        <?php if (! isset($userInfo)): ?>
            <form method="post">
                <div>Username: <input type="text" name="username" /></div>
                <div>Password: <input type="password" name="password" /></div>
                <div><input type="submit" /></div>
            </form>
        <?php else: ?>
            <p>Hello <?php echo($userInfo[0]); ?></p>
            <p>Query executed: <?php echo(htmlspecialchars($query)); ?></p>
        <?php endif; ?>
    </body>
</html>

Notice how no variables have had mysql_escape_string used on them and the unsuspecting author has included a mysql_error() just in case the query fails to tell you what error occurred.

The actual script is here: http://adam.codedv.com/examples/inje..._injection.php

Lets go through some of the steps a malicious user might go through to discover a bit of info about our user table:

1. You know the script might be vulnerable, so lets test it. Enter the following:
   
   Username: '
   Password: any
   
   The script crashes and shows the error.
   
   
2. Now we know that the values aren't escaped, we can have some fun with SQL injection. We simply put any SQL we like into either of the fields and query the table to get the results we want:
   
   Enter "any" into the username field and try the following strings in the password field:
   
   
   
   • To find the admin password:
     ' UNION SELECT password AS username, username AS password FROM user WHERE username='admin
     
     
   • To find the name of the first user:
     ' OR '1'='1' ORDER BY username LIMIT 0,1#
     
     
   • Find the username and password of the second user:
     ' UNION SELECT CONCAT(username, ' ', password),'' as username FROM user#

Now you can see what happens when your variables aren't escaped. Further more the user I set up on my database has only SELECT permissions and access to a single table, preventing any damage. If this were a user for a forum database, it would have access to any of the forum tables. If it were the root user the attacker could even go as far as executing shell commands and creating his own PHP scripts on the web server.

Using mysql_escape_string will cause any single quote marks to be escaped using a backslash \' <--.

When using parametrized queries however, all you need to do is pass the parameters to the query as an array and the database abstraction layer (in your case MDB2 will take care of the escaping for you).

[wwwfilmfilercom]

Thanks for that - I did have a vague idea of this beforehand... so am I right in thinking MDB2 and parameterized queries wont give me any other security advantage? I mean Ill still have to use htmlentities and validate user input correctly?

[visualAd]

Yes you do. With SQL injection you cannot really afford one slip up. You can also automatically html escape any output by utilising a template system such as Smarty.

[wwwfilmfilercom]

How do you validate then? For instance make sure an email is in email format or a number is a number? I looked at the validate package with PEAR but it wont install as its beta. Should I stick to using PHP for this or is there a better package?

[visualAd]

Validation is dependant upon the requirements of the data. Often business constraints depict how a field should be validated. PHP is your best option here.

[wwwfilmfilercom]

Thanks for that! It's cleared up quite a lot for me - the main thing is just seeing how to start the code, I think it doesn't look THAT bad and I can almost grasp it now.

I'm loading PEAR now actually but theres a small problem, how do I set the include_path correctly? Can I just link to the MDB2.php like:

include "pear installation/mdb2.php"

??

[penagate]

include_path is set in php.ini.

[wwwfilmfilercom]

How do I add the file to php.ini? Do I use set_ini or something? Where do I add the set_ini code?

[penagate]

You edit php.ini. I assume, since you mentioned 'loading' PEAR, that you have access to php.ini.

If not, and PEAR is installed, it should already be set, and you don't have to worry about it.

[wwwfilmfilercom]

Ok I've got PEAR::MDB2 installed, I'm pretty sure I can access it ok as I don't get an error message when I require_once("path/MDB2.php");

I'm still confused as to the best way to use it; the links I've looked over have shown what to do for DB etc but they really aren't much practical help in my opinion (maybe one of you guys could work on this!)

Anyway I'm stuck on getting things right. This is a very simple to add items to my levels table:

<?php 
ob_start();
require( "header.php" );

session_start();   
$_SESSION['redirect'] = "levels.php";

require_once("auth.php");

// check levelid is ok for this page
if ($db_level<>1) // user doesnt have access
{
	die(header("Location: notauth.php"));
}

?>

<h3>Levels table!</h3>
<p>Modify the levels table</p>

<form name="levelsform" action="addlevels.php" method="post" onsubmit="return formCheck(this);">
Level name: <input type="text" name="levelname">
<script>
displaylimit("document.levelsform.levelname","",30)
</script><br />
<input type="Submit" value="Add">
</form>

<br />

<?php
include("config.php");

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM Levels";
$result=mysql_query($query) or die( "Unable to do query");

$num=mysql_num_rows($result);

if ($num==0) {
	echo "The database contains no levels";
} else {
	echo "<table><tr><th>id</th><th>name</th><th>update</th><th>delete</th></tr>";

	$i=0;
	while ($i < $num) {

		$levelid=mysql_result($result,$i,"Levelid");
		$levelname=htmlentities(mysql_result($result,$i,"Levelname"));

		echo "<tr>
		<td>$levelid</td>
		<td>$levelname</td>
		<td><a href='editlevels.php?levelid=$levelid'>U</a></td>
		<td><a href='killlevel.php?levelid=$levelid'>D</a></td>
		</tr>";

		$i++;
	}

echo "</table>";

}

mysql_close();

require( "footer.php" ); 
ob_end_flush();
?>

I'm already stuck on how to connect to the Db!!! I'm thinking of doing this:

 $db = MDB2::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" );

But when I've done that am I connected straight away or is there another thing to add?

[penagate]

What does the documentation say?

[wwwfilmfilercom]

This is the link but as you can see there are varied ways: http://pear.php.net/manual/en/packag...ro-connect.php

I have found this page which looks very useful: http://www.phpied.com/db-2-mdb2/

But obviously I'm having to put pieces together bit-by-bit..

[penagate]

As it says there if you use connect() you are connected straight away.

I would use that as I only ever use the data access API directly in one place in my applications, everything else goes through that. So there is no point for me in using singleton().

It also mentions that connection errors are returned as MDB2_Error objects.

$dbh =& MDB2::connect("mysql://$dbuser:$dbpass@$dbhost/$dbname");
if (get_class($dbh) === 'mdb2_error')) {
  // handle an error condition
}
else {
  // good to go
} 

[wwwfilmfilercom]

This is where I'm up to now:

$mdb2=MDB2::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" );
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

$all = $mdb2->queryAll('SELECT * FROM Levels');

I added the 'error' line and found out that I needed to also install the MySQL driver - which was very easy using the package manager (took literally seconds)...

Now I'm looking to get the table data and output it... wish me luck lol...

[wwwfilmfilercom]

FINALLY:

This is the first page I have done, all it does it get data from the Levels table and displays it in a table:

<?php
require("header.php");
include("config.php");
?>

<h1>Levels</h1>
<p>Use this page to modify the LEVELS table.</p>

<form name="levelsform" action="levelsadd.php" method="post">
Level name:<input type="text" name="levelname">
<input type="Submit" value="Add">
</form>

<?php

// CONNECT TO DB
$mdb2=MDB2::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" );
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// GET LEVELS TABLE
$res =& $mdb2->query('SELECT * FROM Levels');

if (($res->numRows())>0) {
	// PRINT TABLE HEADINGS
	echo "<table><th>Levelid</th><th>Levelname</th>";
	// LOOP THROUGH AND PRINT ROWS
	while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) {
		$levelid=$row['levelid'];
		$levelname=$row['levelname'];
		echo "<tr><td>$levelid</td><td>$levelname</td><td><a href=''>update</a></td><td><a href=''>remove</a></td></tr>";
	}
	echo "</table>";
	// FREE MEMORY
	$res->free();
}else{
	// IF NO ROWS IN TABLE
	echo "<p>No data in the table</p>";
}

// CLOSE CONNECTION
$mdb2->disconnect();
	
require("footer.php");
?>

How's it looking?

[penagate]

Good. Well done.

Next step is to start using prepared statements instead of queryAll().

Edit: I see you are in your other thread. I'll reply there.

[wwwfilmfilercom]

Haha I know:

include("config.php");

// CONNECT TO DB
$mdb2=MDB2::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" );
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// GET POST VARS FROM FORM
$levelname=htmlentities($_POST['levelname']);

// CHECK VARS NOT NULL
if ($levelname=="") {
die("Please add a name - <a href='levels.php'> try again</a>");
}

//PREPARE AND EXECUTE INSERT
$types = array('integer', 'text');
$statement = $mdb2->prepare('INSERT INTO Levels VALUES (?, ?)', $types, MDB2_PREPARE_MANIP);
$data = array('', $levelname);
$statement->execute($data);
$statement->free();

// CLOSE CONNECTION
$mdb2->disconnect();

// GO BACK TO LEVELS PAGE
header("Location: levels.php");

The above code is meant to get levelname from the form and check its not empty then insert it.

A few points about it:
1 - Firstly I've used htmlentities there to make sure the levelname doesn't have any characters it shouldn't, I thought I should add it as an extra validation. Not sure if its actually effective though...

2 - Where I've got the 'data' line the first item in the array is meant to be an autonumber so I've left it blank - is that right? (I think you said this is ok in another post).

3 - When I actually test this I get this error:
'Fatal error: Call to undefined function: execute() in /home/b05bite/public_html/investinme/levelsadd.php on line 22'

Line 22 is the one with execute on it obviously, not sure why this is happening as of yet...

[kows]

probably because the execute function doesn't exist. is that the name of the function that executes your queries?

[penagate]

Yes.

It may be that prepare() is returning an error. Check using PEAR::isError() on the return value.

[wwwfilmfilercom]

Like this?

//PREPARE AND EXECUTE INSERT
$types = array('integer','text');
$statement = $mdb2->prepare('INSERT INTO Levels (Levelname) VALUES (?, ?)', $types, MDB2_PREPARE_MANIP);
$data = array('',$levelname);
$statement->execute($data);
$statement->free();
if (PEAR::isError($statement)) {
	die($statement->getMessage());
}

I still get the same error...

[penagate]

No. After you call prepare(), before you call execute().

[wwwfilmfilercom]

Aha - thanks... I'm getting syntax error... which means something is not right with the following:

$types = array('integer','text');
$statement = $mdb2->prepare('INSERT INTO Levels (Levelname) VALUES (?, ?)', $types, MDB2_PREPARE_MANIP);

Looks ok to me... hmmm?? Can you see anything?

[penagate]

You've specified one field name but two placeholders.

Also, you can pass scalars like this:

$statement = $mdb2->prepare('INSERT INTO Levels (Levelname) VALUES (?)');
if (!PEAR::isError($statement))
  $statement->execute($levelname); 

[wwwfilmfilercom]

I copied your code and get the same error - syntax error(!)

Very bizzare, what on earth else needs to be fixed?

[visualAd]

Who is writing the code? You or us? Maybe we should start charging!!!

[wwwfilmfilercom]

Hey sorry Im just asking people who know more than me thats all... I've tried doing this:

PEAR::setErrorHandling(PEAR_ERROR_EXCEPTION);

But that returns another error... MySQL and PHP was relatively easy compared to PEAR thats the only reason I have more questions...

[CornedBee]

Unless you have PHP5 (in which case you should prefer PDO to PEAR::MDB2 anyway), you cannot use exceptions.

[wwwfilmfilercom]

Fixed it!

I used var_dump and getUserInfo() to find out more about the error.

MDB2: syntax error means there is an error with the SQL syntax - which means it was getting prepared incorrectly.

Anyway eventually this was narrowed down to a problem with loading the datatype module AND it all was down to the include_path not being set correctly.

So to clarify for people:

$path = '/home/user/public_html/iim/pear/PEAR';
set_include_path(get_include_path() . PATH_SEPARATOR . $path);
require_once "DB.php";
require_once "MDB2.php";

Add that a file such as config.php and when you 'include' that on every page it should be fine!!!