Simple subscription system in PHP/MySQL

[arioch]

'Ey guys.

I am in the process of teaching myself PHP and MySQL, but need something to get me started. I have decided on the following:

A subscriptionbased band database, where members can sign up their band(s) and it's members, directly on the net via an XHTML form.

I need to:

- know what functions etc. to use to validate form input (for security reasons, avoiding injection attacks etc.).

- and the idea so far is:

- the making of a subscription system, where a "counterfield" in MySQL is incremented by one at a given point in time every day via PHP, and different mails send out after 365, 375 and 385 days respectively. I imagine the given value for each band, being pulled out from the DB as a "daily" variable once every day by a PHP-loop, and then have it checked up against an "if/else" controlstructure. If neither of the specific 365, 375 and 385 days are met, the "daily" variable will be increased by one, and inserted back into the DB. An initial billing mail, and two "reminder mails" is sent to the given band if any of the "if/else" criteria is met, and at day 395, a removal-reminder is sent to the webmaster only, so he can remove the band from the DB.

I have never done any serious programming before, and don't really know PHP or MySQL at all, and I figured something of this size would be a good project to start with, so if somebody could just tell me which functions to use/look at, and the best possible way to go about this (always with security in mind) it would be greatly appreciated. I might as well start learning to code securely now, instead of having to acquire new habits later to change my insecure ways.

Any bids?

[bwkaz]

I am in the process of teaching myself PHP and MySQL, *shudder*

- know what functions etc. to use to validate form input (for security reasons, avoiding injection attacks etc.). And this is why I shuddered. :p

PHP's "default" MySQL interface (i.e. the one everyone seems to use) seems to be really bad at preventing SQL injection. The simplest (and most effective!) way to prevent SQL injection is to simply never concatenate user input into your queries. You do this by using parameterized queries, where the database API takes an entirely separate value for the value of the query (which does come from the user), but does not simply concatenate it into the query.

PHP's default MySQL interface has no way to do this. Even its "advanced" interface makes it a huge PITA compared to other languages' APIs.

If you can, I'd go with either Python or Perl for any kind of database interface scripting language. Python's database API is described here (http://www.python.org/peps/pep-0249.html); it's intentionally generic (supporting several DB backends), supports parameterized queries (using MySQL's native parameter support if you use the MySQL backend, which does not simply concatenate), etc., etc. Perl's is probably documented somewhere, but I've never used it so I don't know for sure.

(Note that PHP's "gpc_magic_quotes" feature, which claims to prevent SQL injection by escaping quotes "magically", is a really poor workaround for this -- for one, quotes aren't the only way to do SQL injection, and for two, they're working on the wrong end of the problem.)

At least you're aware of the issues with security, though. That's miles ahead of a lot of people. ;)

Oh, almost forgot: Don't rely on your SQL injection prevention (in the form of query parameters) to prevent all attacks. XSS would still be possible, for instance, if you ever output strings that a user typed in. Validate all your inputs (Perl's "native" regex support makes this easier than Python -- Python has regex support, but you can't just say "match against /^[A-Za-z0-9]+$/", with the regex right in the code, like you can in Perl). And don't validate with an "I don't want to allow <this list of characters>" type of test -- use an "I only want to allow <this list of characters>" test instead. It's much less problematic in the long run (especially in the presence of UTF-8, where there can be multiple different Unicode code points that represent very-similar-looking characters).

- the making of a subscription system, where a "counterfield" in MySQL is incremented by one at a given point in time every day via PHP, How do you plan on incrementing this field? Cron-job running on the server? Or do you expect someone to hit a certain PHP (or Python, or whatever) page at the proper time each day, without forgetting?

(Server-side web-based scripts can't run at a predetermined time. They run only when a user requests their page.)

It would probably be easier if you just stored the "band entry date" in the MySQL table, instead of a counter. Then you don't have to bother with updating the counter at all; just do something like:

SELECT band_name FROM band_table WHERE CURDATE() - entry_date >= 395
SELECT band_name FROM band_table WHERE CURDATE() - entry_date >= 385 AND CURDATE() - entry_date < 395
SELECT band_name FROM band_table WHERE CURDATE() - entry_date >= 375 AND CURDATE() - entry_date < 385
SELECT band_name FROM band_table WHERE CURDATE() - entry_date >= 365 AND CURDATE() - entry_date < 375

when you want to figure out who to email. Run each query once; this will give you the value of the "band_name" field out of the "band_table" table for all the bands in each category. (Make sure that the data type of the "entry_date" field is date/time, though.) This will make your data less dynamic, too, which might help with performance. (But very likely not.)

(Big picture: You'll probably want to figure out how you want to set up the data first. You might be able to get away with just one table with all the fields you need, but it depends on what all you want to track.)

[chrism01]

If you are going to use PHP, try the PCRE (Perl Compatible RegExes) in re bwkaz's point: http://www.php.net/manual/en/ref.pcre.php

[arioch]

Thanx for you thorough reply, Bwkaz.

Hmmm... Mixed emotions, mixed emotions. The meager programming experience I do have, is with PHP. So I'd like to stick within that.

I was looking for a "timer(hour;minute;second)" function, that could handle the incrementation somewhat like this:


/////////////////////////////////////////////////////////////////////////////////////////
//Example: every 24 hours on the given hour, minute, and second, do this automatically://
/////////////////////////////////////////////////////////////////////////////////////////
timer (2200;29;59){
for (pulling the "day" values from each band from the DB in turn){
If/else {checking if criteria is met, and proper mail reaction executed if yes.}
Insert {he "day" value+1 back into the proper place in the DB after the check is done.
}
}
}

I guess somekind of cronjob would have to do the execution of a PHP routine at a given time... I assume this isn't difficult to implement?

What would a typical "only allow" routine look like when I can't just specify a range of symbols, but have to clear each and every allowed symbol in the field? Let's say I wanted to validate a bandname field:

//
//This is just a wild guess:
//
if (!eregi('a'||'A'||'b'||'B'||'c'||'C'||'0'||'1'||'2 '||'3'... - and so forth, $fieldname)) die(take a hike);
Insert into...;

Along those lines?

Also, I'm not sure I'm really getting your SQL example:

SELECT band_name FROM band_table WHERE CURDATE() - entry_date >= 395

In this case, the "entry_date" field would be in a "date" format and wouldn't be an exact match to the "395" value. Wouldn't that cause a miss when checked by PHP? If the value of "entry_date" field had been 395 instead, it'd be spot on and react. correct?

Or maybe you mean to compare the present date to the stored sign-up date of the band via PHP, and if the present date is 395 days past a bands stored sign-up date (No need to match variables against each other to spot an exact matching string), things are set in motion by PHP, and different mails are sent out according to criteria. Is that what you mean?

I'm a complete spaz at SQL and not much better at PHP, so all of this is new to me. I'm just basking around in the most basic of logic here.

I have spent the evening planning a database and written the SQL for it according to how I think it's supposed to be done. I'm considering getting somebody to look at it and fix/test it through rent-a-coder ASAP.

[bwkaz]

I was looking for a "timer(hour;minute;second)" function, that could handle the incrementation somewhat like this:

/////////////////////////////////////////////////////////////////////////////////////////
//Example: every 24 hours on the given hour, minute, and second, do this automatically://
/////////////////////////////////////////////////////////////////////////////////////////
timer (2200;29;59){
for (pulling the "day" values from each band from the DB in turn){
If/else {checking if criteria is met, and proper mail reaction executed if yes.}
Insert {he "day" value+1 back into the proper place in the DB after the check is done.
}
}
}
In order to do something like that, you would need to have your PHP script running all the time. If it's driving a set of web pages, it won't be running all the time (only when a person requests one of the web pages).

To run it from cron, you'd have to make a separate script that did the checking and the mailing. Then just point to that script from cron.daily (or whatever; the time that cron.daily runs may not be appropriate for your purposes, I don't know).

What would a typical "only allow" routine look like when I can't just specify a range of symbols, but have to clear each and every allowed symbol in the field? Just list the symbols, between the [ and ]. So instead of matching against the regex /^[A-Z]+$/ (for instance), you could match against the regex /^[ABCDE]+$/ -- this would allow only A, B, C, D, and E characters. (And it would also disallow the empty string, because of the + at the end.)


//
//This is just a wild guess:
//
if (!eregi('a'||'A'||'b'||'B'||'c'||'C'||'0'||'1'||'2 '||'3'... - and so forth, $fieldname)) die(take a hike);
Insert into...;
It'd be something like:

if(preg_match('/^[AaBbCc0123]+$/', $_POST['fieldname']) == 0) die('not so fast');

// whatever This does two things: First, it checks against the regex /^[AaBbCc0123]+$/, and second, it makes sure that 'fieldname' is coming from the HTTP POST (as opposed to the URL). There's a security reason for making this distinction, but I don't remember for sure what it is. (:o) It's just automatic reaction now.

preg_match returns 1 if the regex matched the input, and 0 otherwise. (The regex is first, as a string. This is what I meant about PHP being harder than Perl; in Perl, you can just put the regex right into your code, it doesn't have to be in a separate string value. Even though you can do it in PHP with the PCRE library, it's still not as good-looking IMO to have to do something special to get the regex set up.)

Also, I'm not sure I'm really getting your SQL example:

SELECT band_name FROM band_table WHERE CURDATE() - entry_date >= 395

In this case, the "entry_date" field would be in a "date" format and wouldn't be an exact match to the "395" value. Wouldn't that cause a miss when checked by PHP? If the value of "entry_date" field had been 395 instead, it'd be spot on and react. correct? MySQL will do the CURDATE() - entry_date first, and compare that to 395. (CURDATE() returns a date type equal to "the current date on the MySQL server". Subtracting entry_date from that gives the number of days between the two -- the number of days since entry_date, in this case. Then you check to see if that's >= 395, and if so, return the record. Presumably the frontend would then delete these records. In the other cases, it would notify the appropriate people.)

Or maybe you mean to compare the present date to the stored sign-up date of the band via PHP, and if the present date is 395 days past a bands stored sign-up date (No need to match variables against each other to spot an exact matching string), things are set in motion by PHP, and different mails are sent out according to criteria. Is that what you mean? Yes, exactly. :)

[arioch]

Thanx for the info. I believe I now have what I need to get on with things. Much appreciated.