[web] MySQL Conventions
Author
Alright, my current kludge of a newspost system basically works by writing new posts to a .txt file, which is just included in the index page. I'm currently working on converting this to a MySQL-based system, so as to make individual posts easier to manipulate.
I actually have two separate concerns. The first is: my current idea for the SQL system involves echoing the contents of each post in reverse-chronological order every time the main page loads. Is this, well, retarded? I'm basically asking, what is the general convention web developers use for this sort of thing?
My second question is: how secure is it to use mysql_connect() on a publicly-accessible page? Rather than typing out your login credentials in the PHP code every time you want to connect to a database, wouldn't it be smarter to incorporate some password hashing, or something?
Any ideas on either of these things would be appreciated. I swear I'll contribute some advice of my own to this forum when I actually know things. :)
The way you are doing everything is fine. mysql_connect is processed server side, like all mysql code and php, so there is no way the client can know what the php and sql code is. Regarding the displaying of the items in revers chron order, that is fine also. The most important thing regarding mysql is the database layout.
You're off to a solid start.
Dave
You're off to a solid start.
Dave
Author
Oh, alright. That's good to know. I knew PHP was processed by the server, but I didn't want to just assume there was NO way for some guy to take a peek at it before it gets processed. Thanks for the, uh, reassurance.
Both your concerns are valid.
Regarding the first concern, I'm not exactly sure what you mean, so I'll just read into it. It is not a very big performance hit to sort a normal sized result set (ORDER BY foo); after all, that's what databases are designed to do! Just make sure you set up indexes. MySQL can use one index per SELECT / JOIN to speed things up. Thus, it doesn't make sense to index every column. Just index the ones that you will most likely be searching on. If you use something like (WHERE foo=bar) often, then put an index on 'foo'.
However, one thing that people often fail to do is use a WHERE clause. Something like this: SELECT * FROM news ORDER BY post_dt DESC LIMIT 5. Over time, that becomes not so good. It's much better to do this: SELECT * FROM news WHERE DATE_SUB(NOW(), INTERVAL 30 DAYS) <= post_dt ORDER BY post_dt DESC LIMIT 5. Now, you only are sorting the last 30 days (which should contain the last 5 posts), as opposed to a potentially very large number. (After convering a site from MyISAM tables to InnoDB, I found adding such criteria very helpful in improving speed.)
There is another angle to your concern, and that is unnecessary repetition. Generally speaking, for low volume sites it doesn't matter. However, the larger the site gets, the more reason to implement a good caching system. After all, if your content doesn't change, there's no reason to make a lot of database hits. For instance, on a.cc all pages for visitors are cached for up to 15 minutes. The first hit generates the page and stores it in memory. Subsequent hits are pulled directly from the cache with no need to even touch the database.
Your second concern regarding the password is also valid for two reasons: 1) Shared Hosting, and 2) accidental source leakage. However, there's not much you can do about it. You have to put the password somewhere that PHP can read it. However, that means that Apache has to be able to read it. Furthermore, that means anyone on the local box with access to Apache can read it.
Thankfully, most shared hosts implement some sort of security layer that in theory prohibits 3rd party access to the file. Things like PHP's safe_mode and virtual file systems can help out, but nothing is perfect. The bottom line is if you are storing extremely sensitive data, don't use a shared host.
Also, if the server goes crazy, it's possible to leak PHP source. This shouldn't ever happen, but if a sysadmin restarts Apache while forgetting to enable PHP, things could get ugly. In fact, this happened to a site I visited today. The server had crashed earlier, and it was being brought back up. The admin apparently didn't load PHP, because the source was being shown. A look at the include file would have shown the database password. But since you have to supply the password to PHP, encrypting it won't do any good. You'd have to then supply the encryption algorithm as well, which could be stolen almost as easily. (Unless, of course, you have the ability to include PHP files from outside the web tree or enable custom C modules. But if you can do that, there's no reason to put the password in a web accessible PHP file to begin with...)
So basically: if you are storing sensitive data, please don't use a shared host! Otherwise, you should be fine with storing the password in an include file somewhere.
Regarding the first concern, I'm not exactly sure what you mean, so I'll just read into it. It is not a very big performance hit to sort a normal sized result set (ORDER BY foo); after all, that's what databases are designed to do! Just make sure you set up indexes. MySQL can use one index per SELECT / JOIN to speed things up. Thus, it doesn't make sense to index every column. Just index the ones that you will most likely be searching on. If you use something like (WHERE foo=bar) often, then put an index on 'foo'.
However, one thing that people often fail to do is use a WHERE clause. Something like this: SELECT * FROM news ORDER BY post_dt DESC LIMIT 5. Over time, that becomes not so good. It's much better to do this: SELECT * FROM news WHERE DATE_SUB(NOW(), INTERVAL 30 DAYS) <= post_dt ORDER BY post_dt DESC LIMIT 5. Now, you only are sorting the last 30 days (which should contain the last 5 posts), as opposed to a potentially very large number. (After convering a site from MyISAM tables to InnoDB, I found adding such criteria very helpful in improving speed.)
There is another angle to your concern, and that is unnecessary repetition. Generally speaking, for low volume sites it doesn't matter. However, the larger the site gets, the more reason to implement a good caching system. After all, if your content doesn't change, there's no reason to make a lot of database hits. For instance, on a.cc all pages for visitors are cached for up to 15 minutes. The first hit generates the page and stores it in memory. Subsequent hits are pulled directly from the cache with no need to even touch the database.
Your second concern regarding the password is also valid for two reasons: 1) Shared Hosting, and 2) accidental source leakage. However, there's not much you can do about it. You have to put the password somewhere that PHP can read it. However, that means that Apache has to be able to read it. Furthermore, that means anyone on the local box with access to Apache can read it.
Thankfully, most shared hosts implement some sort of security layer that in theory prohibits 3rd party access to the file. Things like PHP's safe_mode and virtual file systems can help out, but nothing is perfect. The bottom line is if you are storing extremely sensitive data, don't use a shared host.
Also, if the server goes crazy, it's possible to leak PHP source. This shouldn't ever happen, but if a sysadmin restarts Apache while forgetting to enable PHP, things could get ugly. In fact, this happened to a site I visited today. The server had crashed earlier, and it was being brought back up. The admin apparently didn't load PHP, because the source was being shown. A look at the include file would have shown the database password. But since you have to supply the password to PHP, encrypting it won't do any good. You'd have to then supply the encryption algorithm as well, which could be stolen almost as easily. (Unless, of course, you have the ability to include PHP files from outside the web tree or enable custom C modules. But if you can do that, there's no reason to put the password in a web accessible PHP file to begin with...)
So basically: if you are storing sensitive data, please don't use a shared host! Otherwise, you should be fine with storing the password in an include file somewhere.
In relation to what konForce was saying RE: database password accessibility in the case of a server FUBAR -- there are a few simple things you can do to avoid giving out important passwords.
Firstly: If you are the superuser of the system that MySQL is working on -- you have the ability to add users. Don't underestimate the value of this! You can create a user with very limited access to the system -- that you might use purely for the site. Meaning therefore that this user account cannot create new tables, mess with existing DBs -- only alter/add/delete rows from current tables. Read up on adding users at MySQL.com and it'll give you some pointers.
The point of the above -- obvious to you by now, is damage control -- if a malicious user has no access to anything save the simplest stuff for the page --hopefully they're stuck trying to break the admin protection before they can touch anything else.
Secondly -- as far as design is concerned, caching is very useful -- if you can predict when content is gonna be static, you should definitely make sure you try to cache it client side (you can do this via the use of Header() and the 'Expires' part of the HTTP header that gets sent with an HTTP response -- look up the Header() function in PHP docs to see about this...)...and definitely -- avoid mysql_connect calls if you can -- if you have five separate things on a page that come from the DB, you can always use a persistent connection for all of them (i.e. write a small include file for database operations that establishes a connection at the start of your script and closes it at the end). That said, not everyone likes this method -- but I find it works well.
Think I said everything I wanted to say...good luck with the page and let us know if you need more help!
~Shiny
Firstly: If you are the superuser of the system that MySQL is working on -- you have the ability to add users. Don't underestimate the value of this! You can create a user with very limited access to the system -- that you might use purely for the site. Meaning therefore that this user account cannot create new tables, mess with existing DBs -- only alter/add/delete rows from current tables. Read up on adding users at MySQL.com and it'll give you some pointers.
The point of the above -- obvious to you by now, is damage control -- if a malicious user has no access to anything save the simplest stuff for the page --hopefully they're stuck trying to break the admin protection before they can touch anything else.
Secondly -- as far as design is concerned, caching is very useful -- if you can predict when content is gonna be static, you should definitely make sure you try to cache it client side (you can do this via the use of Header() and the 'Expires' part of the HTTP header that gets sent with an HTTP response -- look up the Header() function in PHP docs to see about this...)...and definitely -- avoid mysql_connect calls if you can -- if you have five separate things on a page that come from the DB, you can always use a persistent connection for all of them (i.e. write a small include file for database operations that establishes a connection at the start of your script and closes it at the end). That said, not everyone likes this method -- but I find it works well.
Think I said everything I wanted to say...good luck with the page and let us know if you need more help!
~Shiny
Quote:Original post by Shiny
Secondly -- as far as design is concerned, caching is very useful -- if you can predict when content is gonna be static, you should definitely make sure you try to cache it client side (you can do this via the use of Header() and the 'Expires' part of the HTTP header that gets sent with an HTTP response -- look up the Header() function in PHP docs to see about this...)
Doing server-side caching is even better. If you use a template system like smarty then that will take care of caching for you. You can also do it yourself. Simply have the php script output to a file. If the timestamp of that file is e.g. within 15 minutes of the current time, then output the file instead of running the script. Pseudo-code for index.php:
if exists(cache/index.html) and time - timestamp(cache/index.html) < 15 * 60 then output cache/index.htmlelse <run script> create cache/index.htmlQuote:...and definitely -- avoid mysql_connect calls if you can -- if you have five separate things on a page that come from the DB, you can always use a persistent connection for all of them (i.e. write a small include file for database operations that establishes a connection at the start of your script and closes it at the end). That said, not everyone likes this method -- but I find it works well.
That's not a persistant connection. Persistant connections are connections that stay open between page calls and are created with mysql_pconnect. You should not use this. The technique you describe is solid though and works with the regular mysql_connect.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
