[RESOLVED] MySQL enquirey

[PartialReptile]

Hey,

I’ve just started using MySql (databases full stop for that matter), and I’m lost. I’ve gotten as far as (with PHP) creating database cells, columns, rows etc and inserting data into them, but I can’t figure out how to do the following.

I have a cell called customers, with columns; ‘customerID’ as the primary key and then also, ‘LastName’, ‘FirstName’, ‘Phone’;

I have a variable requested from a URL, lets call it $customer (and also I have $lName and $fName and $phone similar).

And I want to first check if $customer is in the database as the primary key(customerID column), and if it is, I want to check if the value of the LastName column in that row is equal to $lName, and if that is; check if the value of the FirstName column in that row is equal to $fname and so forth and so forth.

Probably very simple, I know, but I’m scratching my head here. Any help would be great. Cheers.

[Neumy]

Typically, I'd ask to see the code you've written so far and help guide/correct you that way, but I will supply some code to give you a head start. Please understand that there are many ways to do this - this is only one way. (My code has a custom DB call - I'm assuming you have already made DB connection.)

// Pull data from the GET
$customer=""; $lName="";  $fName=""; $phone="";
if(isset($_GET['customer'])) $customer = stripslashes($_GET['customer']);
if(isset($_GET['lName'])) $lName = stripslashes($_GET['lName']);
if(isset($_GET['fName'])) $fName = stripslashes($_GET['fName']);
if(isset($_GET['phone'])) $phone = stripslashes($_GET['phone']);

// Getting `customerID` info from DB
// NOTE: I have some custom DB call ($db->getRow() )
$lNameCheck=""; $fNameCheck=""; $phoneCheck="";
$query = "SELECT `LastName`,`FirstName`,`Phone` FROM `customers` WHERE `customerID`=$customer LIMIT 1";
if($row = $db->getRow($query))
{			
	$lNameCheck = $val['LastName'];
	$fNameCheck = $val['FirstName'];
	$phoneCheck = $val['Phone'];
}

// Error Checking DB results
$error=0; // set to no error
if($lNameCheck=="")
{
	// "Error: No customer found with stated CustomerID"
	header("Location: errorMessage.php?error=notFound");
	exit;
}
elseif($lNameCheck!=$lName)
{
	// "Error: Entered name does not match DB name"
	header("Location: errorMessage.php?error=noMatch");
	exit;
}
elseif($fNameCheck!=$fName)
{
	// "Error: Entered name does not match DB name"
	header("Location: errorMessage.php?error=noMatch");
	exit;
}

1) Pull the data from GET. Honestly, it's very dangerous to pull data from either GET or POSTS that have any direct connection to the DB ($customer). You might want to up the security on my code with a more advanced stripslashes function to ensure that the user is not hacking your DB.

2) SQL. I'm assuming that the single $customer variable will link to only 1 row in the DB. All you really need to do is pull the value out of the DB for $customer and check the values.

3) My Error Checking section is very basic and unfinished as I don't know what you want to do with each check. But hopefully this gives you an idea on what you can do.

If you're still having trouble, post your code and we can check to see what is going on. Good luck!

[PartialReptile]

Hi Nuemy,

Cheers for the reply. It will take me a while to mull over this, but from what I can see I think it is exactly what I want. However, as I said I am completely new to MySQL and databases so I am very interested in line ‘Honestly, it's very dangerous to pull data from either GET or POSTS that have any direct connection to the DB’; I’, not sure I am though. I’m still working through the code, and it seems to be working fine, but I haven’t actually reached the part that I needed the above for, so I can’t really post it yet.

Ultimately, I’m trying to sell digital downloads by using PayPal’s IPN (or whatever it’s called) system. So, I have a PHP file that does all the background stuff with PayPal - takes in some information like the payers first and last name, email, and the name of the file they purchased. And, most importantly, checks if they have actually made a payment and it has processed okay.

If it has, I’m then creating a unique identifier for them (first 4 characters of their first name, first 4 of their last and 4 random characters - all of which are inserted into the DB’s primary key (which is varchar(12) as a 12 char ID for that purchase, the other cells are the file they purchased (download.zip), their email address (just cause), and a column that has the number 0 in it.

Then it emails them a download link like:
http://www.mysite.com/download.zip?token=(The primary key id)

Now, all of the above I have done and (seems) to be working correctly.

What I’m working on now, and why I was asking the above.

I have the download files in a directory (actually, a sub, sub, sub directory) with a .httpaccess file in the first directory that takes any non-standard files and forces them to process via that directory’s index page as opposed to direct linking. The index page is basically a script that forces a download dialogue for the file.

Again, this I already have working. But obviously I need to confirm that it’s an authorised user (aka one that’s paid) that’s accessing the files.

So what I want to do is grab the token id and file from the above link (both of which I can do grand), but I then want to check that A: the token ID is valid (exists in the D😎, and B: check that the file the user is trying to download is actually the file that’s in the same row as the token. And also that the column with the number 0 in it is less than or equal to 3

And then only force the download dialogue for the file is the above is true, otherwise bounce them to an error.

I’m also updating the column with the number 0 in it by 1 each time a user visit’s the download link so that the I can restrict the amount of times they download a file (similar to above, allow the download dialogue if the number is 0,1,2 or 3, but error if it’s more than three.

It’s the securest way I can think of doing this (although, god knows, I’m probably travelling from Ireland to England via America).

Although, you’re completely right I have no idea how to handle security with a data base. Most of the columns I’m writing to in the creation script have set values, the id one being varchr(12), the number one being varchar(1) and the file and email ones being varchar(200). The only access I’ll have to the database in the verifying/download page will be to check if values correspond to those in the link and updating a varchar(1) column.

As well as this, I’ve removed the mysql username and PW from the actual file and placed in a non-public directory then used an internal directory include for it
include (“/home/use/directory/file_with_passwords.php&#8220😉;

If you can point out obvious pitfalls, security wise with using the db in this manner, please do.

Yeah, that WAS a long post

[Neumy]

Hehehe! Sounds like a cool project. As for the security with the DB, I'm not an expert but I have played around.

Try this link: http://us3.php.net/manual/en/function.mysql-real-escape-string.php

There are also other ways to help restrict/check any input that makes it into your SQL statement; if I run across any, I'll try to post them here for you. I hope this helps.

[PartialReptile]

Hi can anyone see where I'm going wrong with the below. All the DB connect stuff seems to be working, I think the problem is just in actually loading / retrieving the data from the database because if I try to echo the values (as below), I just get a white-out. I presume the problem is with mysql_fetch_row, that's it's maybe not the right function??? or not??? Cheers.

$allowed = strip_tags( stripslashes($_REQUEST['f']) );
$check = strip_tags( stripslashes($_REQUEST['d']) );

//Check for requested vars
if( (!$allowed) or (!$check) )
{
	echo 'you are not authorised to view downloads on this server';
	exit();
}

//Mysql connect, select DB and fire query
$con = mysql_connect("localhost","$mysqlUN","$mysqlPW")
	or die ('could not connect to database');
$rs = mysql_select_db("sample_dataBase", $con)
	or die ('could not select database');

$allowed = mysql_real_escape_string($allowed);	
$check = mysql_real_escape_string($check);

$sql ="SELECT * FROM customer WHERE Dib=\"$check\" and File=\"allowed\"";
$rs = mysql_query($sql, $con)
	or die ('Could not validate');
$num = mysql_num_rows( $rs );

//if there is a match (only 1)
if( ($num != 0) or ($num <= 1) )
{
$row = mysql_fetch_row( $rs );

	$checkToken = $row['Dib'];
	$checkFile = $row['File'];
	$checkDownloads = $row['Dibby'];

	echo $checkToken . '<br />';
	echo $checkFile . '<br />';
	echo $checkDownloads . '<br />';			
}
else
{
echo 'Could not validate 2';
exit();
}

[PartialReptile]

Anyone? 🙁

[laserlight]

I presume the problem is with mysql_fetch_row, that's it's maybe not the right function?

It can be used in this case with some modification of your code, but since you want an associative array, you should use mysql_fetch_assoc() instead.

By the way, strings in SQL are single quoted, so you should write:

$sql ="SELECT * FROM customer WHERE Dib='$check' and File='allowed'";

Your original SQL statement probably still works with MySQL as long as standard compatibility mode is not enabled.

[PartialReptile]

aha! (as in surprise, not the band), the funny, mixed-up, multi-coloured letters didn't work. They do now 🙂 Thank you laserlight.