A Few Easy Newbie PHP/MySQL Questions

[dcsonic2k]

OK. I'm new to PHP and I've got a few questions. I'm trying to follow the tutorial on 'Building a Database-Driven Website Using PHP and MySQL' and I just need to know a few things.

1) How do I put my MySQL access settings in an include file and then call that file from where the MySQL connect string would go?

2) How can I make sure that you can't put select statements and that kind of stuff in the URL line? Is it even possible for people to do a custom select statement from a PHP script or would you have to use a bunch of variables for that?

3) In my database, I'm trying to store a list of articles. Each article has a 'Title' and a 'PostDate' column. Additionally, I have an 'ArticleLookup' table that stores what the author of that article is. How can I query for a list of articles by a certain author and have it return the titles of the authors last 20 titles?

Thanks for answering these questions, however simple they are. I'm sure I'll probably have more later.

Thanks.

[TWTCommish]

Hi dc,

1) - Create a file called, say, "connect.inc", and put this inside:

<?php

    $db = @mysql_connect("localhost",
             "USERNAME", "PASSWORD");
    if (!$db) {
      echo( "<P>Unable to connect to the " .
            "database server at this time.</P>" );
      exit();
    }

    if (! @mysql_select_db("DATABASENAME") ) {
      echo( "<P>Unable to locate the " .
            "database at this time.</P>" );
      exit();
    }

Replace the all-capital letters with your own information, of course. Place this "connect.inc" file one tier above your web directory, so that it's not accesible from the web. When you want to connect to the database in one of your files, use this command:

include("../connect.inc");

That's how you store the connection information seperately, so you can call upon it as you please, and keep it away from anyone with malicious intent.

2) - Can you rephrase this? I don't believe I understand.

3) - More information will be needed. Can you give us a listing of every field you have in both the Author and Article table? But so you know: yes, It should be quite possible.

[PeterW]

Re; 2) do you mean if you had a script called by
"script.php?something=something"
and you had an SQL select with..
SELECT a FROM b WHERE a='$something' ?
I haven't really given this much thought... but I suppose the only thing they could do is change the ordering and limits or basically any of the commands that proceed WHERE..
However, if you use SELECT $something FROM $somethingelse, then the person has the ability to select private things from any table in the current db!
I think this can be remedied by a bit of careful planning and if necessary some regular expressions...


re; 3) if by "'ArticleLookup' table" you mean you have ArticleLookup as another column... you can do "SELECT Title, PostDate, ArticleLookup FROM tablename WHERE ArticleLookup='$author' ORDER BY PostDate DESC LIMIT 0,20"

something like that should do

[dcsonic2k]

Thanks for the responses guys. Let me clarify (sorry, it was kinda early in the morning when I wrote these):

2) My current query doesn't use any variables in the actual query (not yet, anyways, not at least until i get into search features). So if I don't use a variable(s) for the select statement, then there should be no problem? (I store the info fed back from the query in some variables, and I've noticed that if you type in the variables from the URL line, it will replace the ones MySQL gets with the one the user supplies)... Is this a problem?

Ex: if you type in "script.php?Title=Whatever_I_Want" it would replace the normal text in the page, say "Test Article" with "Whatever_I_Want" Are all scripts like that?

3) Okay, here's the structure of the part of my database that matters for this:

http://www.geocities.com/dcsonic2k/dbdsgn.gif

Hope that helped. I'm really starting to like this PHP and MySQL stuff. It makes it nice and easy to make a site. Thanks again for the answers, guys.

[eKo]

I'm only going to answer question 3, as q1 is already answered and I don't know enough PHP to answer the second one.

I looked at your tables and it seems to me that the ArticleLookup table is in fact obsolete. You'd better add AID and CID to the Article table, this would make it easier to search. The query to find the last 20 articles by an author, where the lastname of the author is stored in $author could be like this:

//connect to your db
$db = mysql_connect("myhost", "username", "password"); 
mysql_select_db("mydb", $db); 

//this statement selects the AID from lastname stored in $author
$sql="SELECT id FROM Authors WHERE Lastname=$author";
$result=mysql_select($sql, $db);
$auth_id=($result, 0, "id");
$auth_firstname=($result, 0, "Firstname");

//this statement selects the last 20 articles from an author and sorts them by last PostDate first
$sql="SELECT * FROM Articles WHERE AID=$auth_id ORDER by PostDate DESCENDING LIMIT 20";
$result=mysql_select($sql, $db);

//use this loop to show the articles
where ($row=mysql_fetch_array($result))
{ //do this to put the Article category in $category, so you can show that too
  $sql="SELECT * FROM Categories WHERE id=$row["CID"];
  $result2=mysql_select($sql, $db);
  $category=($result, 0, "CatName");
  and then show the all info here
};

I haven't tested it, but I think this should work. I'm sure there is a more elegant way to do this, but I'll leave that to someone else ;-).

[Karl]

Q2:

If you pass variables to the script e.g. script.php?query=dogs and you then use query in your select e.g. "SELECT * FROM table WHERE group=\"$query\"" you are vulnerable to someone putting another select on the end of the query= part of the url. To prevent this you can use "SELECT * FROM table WHERE group='".$query."'" as the ' are designed to prevent the another query being appended to the data.

[JakeJeck]

#2

Require the server to get HTTP_REFERER and check that it came from your domain.

If someone types http://www.yoursite.com/stuff.php?var=whatever it won't process anything. That also might prevent anyone from linking to your articles from another site which you may or may not want....ie someone posts a link in a BBS to an article and it wouldn't display since they didn't come from your domain.

Of course you could check for just a blank referer, but then someone could link to http://www.yoursite.com/stuff.php?var=whatever on their page and it would try to process it.