Making A Link = Mysql

Feelay

Feb 18 2008, 03:16 PM

Hey!

I will try to make this as clear as possible.

how can I make the following.

I have a list, of all members on my site. If I press on a members name(link), I will come to his profile.
To come to his profile, I need to get out some vaule from the database, but to get out some value from the database, I must tell the code, how it should know who the user is (hard to understand?).
To do that, I must add a mysql_query in the code ( I think), like "SELECT user FROM dbname WHERE user=link".. This is just how I think it works. I know it is kinda wrong.. but I don't know how much. Can anyone please help me on the line ?

(Sorry if you didn't understand..)

Comment/Reply (w/o sign-up)

vujsa

Feb 18 2008, 04:14 PM

Well, we use query urls for the job. Like so:

www.domain.com/index.php?username=vujsa

It would be better to use a user id instead as there could be characters in the username that will have trouble in the url like spaces. But, it is up to you.

Anyhow, here is the PHP needed to read the url provided:

CODE

$_GET['username'] = $username;

mysql_query("SELECT `user` FROM `dbname` WHERE `user` = '$username'");

I usually change the super global variable $_GET to a regular variable since they can get tricky to insert into some types of strings.

I would suggest adding a few lines to ensure that whatever the link contains is valid data and now an attempt to inject data into your database.

That should just about do it for you.
I'm sure this will provide you with a lot of ideas and questions. Good luck with your project.

vujsa

Comment/Reply (w/o sign-up)

Feelay

Feb 18 2008, 07:13 PM

hmm. I still don't understand.

How can I make a normal

CODE

link into a $GET['..'] variable?

Comment/Reply (w/o sign-up)

mastercomputers

Feb 19 2008, 08:45 AM

Hey Feelay,

What vujsa means is that if you had a link like

CODE

<a href="index.php?username=someuser">someuser</a>

Then that would create a $_GET['username'] variable with the value 'someuser' for the index.php page.

I notice vujsa is slipping though, his code should be

CODE

$username = $_GET['username'];

Doing it his way round, you would get an undefined variable trying to be assigned to a $_GET item, I'm not actually sure if they can be modified either, I should probably test that just out of curiosity.

By the way, I don't like this method of using a $_GET request to be inserted into a mysql_query, this just sounds warning bells.

If you notice the links to members here, they have been rewritten to suggest they are .html pages, this is just for SEO because bots don't like pages with get requests. The $_GET part is the m## where ## is a number that represents the member's id, that is the only information that is really relevant in these links, and will allow you to discover other members by just altering the m## part, maybe it poses SQL injection exploits, but I don't really have time to test but I'm sure others have already attempted to exploit it and IPB may have solved the problem.

Cheers,

MC

Comment/Reply (w/o sign-up)

vujsa

Feb 19 2008, 09:01 AM

QUOTE(mastercomputers @ Feb 19 2008, 03:45 AM)

Hey Feelay,
What vujsa means is that if you had a link like

CODE

<a href="index.php?username=someuser">someuser</a>

Then that would create a $_GET['username'] variable with the value 'someuser' for the index.php page.

I notice vujsa is slipping though, his code should be

CODE

$username = $_GET['username'];

Doing it his way round, you would get an undefined variable trying to be assigned to a $_GET item, I'm not actually sure if they can be modified either, I should probably test that just out of curiosity.

By the way, I don't like this method of using a $_GET request to be inserted into a mysql_query, this just sounds warning bells.

If you notice the links to members here, they have been rewritten to suggest they are .html pages, this is just for SEO because bots don't like pages with get requests. The $_GET part is the m## where ## is a number that represents the member's id, that is the only information that is really relevant in these links, and will allow you to discover other members by just altering the m## part, maybe it poses SQL injection exploits, but I don't really have time to test but I'm sure others have already attempted to exploit it and IPB may have solved the problem.

Cheers,

MC

, yeah I missed that! Sorry about that. Been kind of tired lately I guess.

Anyway, long time no see mastercomputers.

Anyway, what MC told you is correct. You really need to protect yourself by checking the inserted data carefully before sending it on to the SQL query.

Other than that, I think that you should be well on your way.

vujsa

Comment/Reply (w/o sign-up)

Feelay

Feb 19 2008, 11:13 AM

QUOTE

<a href="index.php?username=someuser">someuser</a>

hmm.. lets say I have 100 members. I think it would take a very long time to change the index.php?username=someuser to all the members or? can I write something else instead of "someuser"=?

Maybe this would work?
If I make a for loop, (or while or whatever) an let it show all the names as a link, were the "someuser will be replaced with the "someuser" value.. would that work?

Comment/Reply (w/o sign-up)

Mordent

Feb 19 2008, 01:11 PM

I did something very similar to this just yesterday, in fact. I'll see if I can rummage up my little snippet of code for you and tweak it to make it more generic. *rummages*

The code below should be all together, but I've stuck a load of comments and whatnot before each 'chunk' to explain a little more about what I'm doing.

First, we access the database. I did this in a seperate file, which I used require to open up here. Note that I defined a variable (not actually 'SomeAccessCode', but even that would work), which db.php checks whether or not is defined. If it is, it connects to the database etc.

CODE

<?php
// access database
define('SomeAccessCode',true);
require('includes/db.php');

The next chunk runs a query on the database table 'members', ordering them by id and retrieving the username. Technically you don't need to order them, but it makes sense to do so for me. This bit also counts up how many rows (i.e. members) you have, ready for the loop next...

CODE

// get the usernames of the members
$getMembers = mysql_query('SELECT username FROM members ORDER BY id') or die(mysql_error());
$numMembers = mysql_num_rows($getMembers);

This bit here irked me for a while, and still does to some extent. Using a for loop probably isn't the best way, but it works for one thing. Anyone care to mention a neater way of doing this? Anyway, the point is that it cycles through the members, each time creating an array with the data in that row and using echo to put that in an unordered list with each member on their own line. The link will point to a page called member_profile.php, where their name can be extracted using $_GET['username'], and on that page further queries can be made to the database to get whatever information you want to show about them. Note that I put a new line after each echo, but that's just me being fussy.

CODE

// display each member's name, with a link to their profile
echo '<ul>
';
for($count = 1; $count <= $numMembers; $count++)
{
$row = mysql_fetch_array($getMembers);
echo '<li><a href="member_profile.php?username=' . $row['username'] . '">' . $row['username'] . '</a></li>
';
}
echo '</ul>
';
?>

If you wanted, you could point the link to index.php instead (with the username still given) and check at the beginning if $_GET['username'] is set (using the isset() function), which I think should deal with it nicely. Bear in mind the security implications of the whole thing, of course, which I only really just touched on here by having db.php check if it was being called by an 'internal' script.

As for making the pages .html, if you're worried about SEO then I'll let someone else take over, as it really isn't my field.

Hope this helped!

Comment/Reply (w/o sign-up)

Feelay

Feb 28 2008, 04:48 PM

thank you guys

Now. what is the best way to avoid SQL injections ?

Comment/Reply (w/o sign-up)

ethergeek

Feb 28 2008, 06:27 PM

QUOTE(Feelay @ Feb 28 2008, 09:48 AM)

thank you guys

Now. what is the best way to avoid SQL injections ?

I was just about to say something to this effect reading through this thread...the code vujsa posted up there does nothing to sanitize database inputs. Brings http://xkcd.com/327/ to mind.

Check out this function in PHP to sanitize your inputs: http://us.php.net/manual/en/function.mysql...cape-string.php.

Comment/Reply (w/o sign-up)

Making A Link = Mysql_query

Making A Link = Mysql_query