Advice on structuring programs?

[Topazan]

I have a lot of experience with AS3, so I thought I would be experienced enough with programming to try a browser game again.

It's really confusing to me how you're basically working with 3-4 languages.  You have to interact with the user through html and javascript, process the data with PHP, and interact with the database through SQL.  For this reason, I'm having a lot of trouble keeping my code readable.  I was wondering if anyone had any advice or tips.

How much do you use the SQL language?  Do you just prepare queries with PHP, or do you use transacts and processes?

Do you make each "command" its own page, or would you bundle them together?

As I see it, these are the tasks that need to be completed:
1.  parse the command
2.  run a sanity check on the command, which may require data from the database
3.  execute the command on the database
4.  display the page, utilizing the output from the previous.

I'm thinking that 2 and 3 might be best done using sql processes, but I'm not sure.

Anyways, I'm just rambling.  Does anyone have any advice?

[Chris]

Disclaimer: the stuff below was written for people who are confused, once you get grasp of it you might do things otherwise.


You are writing everything in PHP. PHP is the language you write it in. And without doubt, PHP is the core of your writing (you can replace "PHP" with "Ruby of Rails" if you happen to hate PHP ). That will set you in the proper state of mind.

You don't use HTML. You use echo();. HTML is just a parameter passed to echo function. And that's all.

You minimize use of SQL since it is very, very, very expensive processing time wise. Precompute what you can on PHP, then pass the minimum to SQL.
PHP good, SQL bad

As for structure take a look at C/C++. C is industry standard for a reason. PHP is just dumbed down C (I like to call it "C for kindergardeners" ) so almost everything aplies here as well. Check how the big brother of PHP did it.

[JGadrow]

Honestly, if you're just starting out, don't worry about it so much. Figure out a way that suits your needs for the present and explore other options.

Eventually, most professionals opt for a template engine that's responsible for the presentation / output of your program. That way, your PHP code doesn't really care if it's outputting HTML, JS, CSS, XML, etc.. And usually an object is developed that controls your interaction with the database.

But, again, for the time being, just do it however it seems to work and then review your process afterward and tweak it. Once you start doing maintenance on a particular program, you'll start learning better ways to structure it: "Oh, man! I should have put X over here in the xyz.php file!"

[Topazan]

Thanks for the advice.

You are writing everything in PHP. PHP is the language you write it in. And without doubt, PHP is the core of your writing (you can replace "PHP" with "Ruby of Rails" if you happen to hate PHP  ). That will set you in the proper state of mind.

You don't use HTML. You use echo();. HTML is just a parameter passed to echo function. And that's all.

That would be fine, if I didn't have to worry about making forms for everything.

I think what I'll do next time is start with a page containing only a command line and space for output, and worry about building the actual pages when I'm done.

You minimize use of SQL since it is very, very, very expensive processing time wise. Precompute what you can on PHP, then pass the minimum to SQL.
PHP good, SQL bad  

Really?  Why?  Do you just mean minimize time spent searching the database, or are SQL scripts really that much less efficient than PHP scripts?

My problem with SQL has been having to use these ridiculous constructs.  (I do all my PHP in HaXe now, so I may have the syntax wrong.)

Code: [Select]

db.query("SELECT * FROM Items WHERE id='".$itemID."' AND type='".$type."';");I had thought that prepared statements would have made the code look cleaner.  Is there a better way to do it?

As for structure take a look at C/C++. C is industry standard for a reason. PHP is just dumbed down C (I like to call it "C for kindergardeners" Cheesy) so almost everything aplies here as well. Check how the big brother of PHP did it.

I'm not sure what you mean here.  Surely different programmers had different styles in C, as in PHP.  Maybe structure has some special meaning that I'm not aware of, I just meant the way the code is organized.

Honestly, if you're just starting out, don't worry about it so much. Figure out a way that suits your needs for the present and explore other options.

Eventually, most professionals opt for a template engine that's responsible for the presentation / output of your program. That way, your PHP code doesn't really care if it's outputting HTML, JS, CSS, XML, etc.. And usually an object is developed that controls your interaction with the database.

But, again, for the time being, just do it however it seems to work and then review your process afterward and tweak it. Once you start doing maintenance on a particular program, you'll start learning better ways to structure it: "Oh, man! I should have put X over here in the xyz.php file!"

I'll keep that in mind.

Thanks, Chris and JGadrow.

[JGadrow]

My problem with SQL has been having to use these ridiculous constructs.  (I do all my PHP in HaXe now, so I may have the syntax wrong.)

Code: [Select]

db.query("SELECT * FROM Items WHERE id='".$itemID."' AND type='".$type."';");I had thought that prepared statements would have made the code look cleaner.  Is there a better way to do it?

Prepared statements would definitely make this look cleaner:

Code: (sql) [Select]

SELECT * FROM Items WHERE id=? && type=?;
However, not every query needs to be done using a prepared statement. Prepared statements actually have a higher resource (albeit a slight one) than do standard queries. The exception here is if you're using prepared statements to execute a batch of statements where only the parameters change. This is because, to prepare the query, you have to make one more communication with the SQL server. It's not a huge performance cost, but each query increases that cost. So, just be aware that it's a place to potentially optimize your code if you don't really need the security there.

As another suggestion, you could use the sprintf() function to make the code look a little cleaner if that's the only objective you really need to accomplish. I feel this is a good time to tell you to beware of just blindly trusting input though. With no boilerplate surrounding this, it looks wide open to SQL injection.

Code: (php) [Select]

$query = sprintf('SELECT * FROM Items WHERE id=\'%s\' && type=\'%s\';', $itemID, $type);

[dsheroh]

However, not every query needs to be done using a prepared statement. Prepared statements actually have a higher resource (albeit a slight one) than do standard queries. The exception here is if you're using prepared statements to execute a batch of statements where only the parameters change. This is because, to prepare the query, you have to make one more communication with the SQL server. It's not a huge performance cost, but each query increases that cost. So, just be aware that it's a place to potentially optimize your code if you don't really need the security there.

This is one of the few places where JGadrow and I seem to disagree.  I love me some prepared statements.  I would say that (in the ideal world, at least) every query which contains data built from untrusted input (like, say, user-supplied values) should be done using a prepared statement.  Although this can lead to marginally lower performance, it's a vanishingly small loss in any case (unless your PHP code and the database are running on separate machines with a very slow network between them) and, if your performance needs are demanding enough that such a minor difference actually matters, then you should be running your code in a persistent application environment (e.g., FastCGI) and caching your prepared statements from one request so that subsequent requests can reuse it and avoid the overhead of re-parsing the query every time it's run.

As another suggestion, you could use the sprintf() function to make the code look a little cleaner if that's the only objective you really need to accomplish. I feel this is a good time to tell you to beware of just blindly trusting input though. With no boilerplate surrounding this, it looks wide open to SQL injection.

Code: (php) [Select]

$query = sprintf('SELECT * FROM Items WHERE id=\'%s\' && type=\'%s\';', $itemID, $type);

This can be trivially cleaned up a bit more by using double quotes around the format string:

Code: (php) [Select]

$query = sprintf("SELECT * FROM Items WHERE id='%s' && type='%s';", $itemID, $type);
But, really, use the prepared statement.    It'll give you absolute protection from SQL injection attacks without the overhead (cognitive or processing) of having to escape data before giving it to the database.

(...which brings up something that hasn't occurred to me before.  How does the performance impact of doing separate prepare-and-execute of a query that only gets run once compare to the performance impact of escaping user inputs?  There's a non-zero cost for calling mysql_real_escape_string, after all, so it's not a case of "escape-and-interpolate is free, prepare-and-execute isn't".)

[JGadrow]

This can be trivially cleaned up a bit more by using double quotes around the format string:

Code: (php) [Select]

$query = sprintf("SELECT * FROM Items WHERE id='%s' && type='%s';", $itemID, $type);

True, but I don't think the slashes were that much worse. More likely than not, at least one of those parameters is actually an integer field. But, personally, I use single-quoted strings because it represents a string literal. Using double-quotes on this statement results in unnecessary processing (double-quoted strings search for variables within the string to expand into values).

But, really, use the prepared statement.    It'll give you absolute protection from SQL injection attacks without the overhead (cognitive or processing) of having to escape data before giving it to the database.

Yeah, I agree that this case is probably best to use for security. I do not use prepared statements when I have a literal, known value that will be used with the query. But, yeah, I prefer to use them whenever the input is unknown.

(...which brings up something that hasn't occurred to me before.  How does the performance impact of doing separate prepare-and-execute of a query that only gets run once compare to the performance impact of escaping user inputs?  There's a non-zero cost for calling mysql_real_escape_string, after all, so it's not a case of "escape-and-interpolate is free, prepare-and-execute isn't".)

This is also a great point that I believe will convince people to utilize prepared statements. But hardly relevant in my case. When I'm not using prepared statements, I have no need to escape the data since it's known to be valid.

[Chris]

Use HEREDOC for forms. Anyway, it is more about state of mind. As long as you will think about HTML as a parameter of PHP it's enough.

Why SQL is the root of all evil: http://community.bbgamezone.net/index.php/topic,2204.0.html

"structure" has so many meanings that it is pointless to worry about being misunderstood Just do it like everyone else who program in C, that's my advice.

I disagree with both JGadrow  and dsheroh Do not use prepared statements. At least at the beginning. Get the hang of pure raw SQL syntax first.

[dsheroh]

I disagree with both JGadrow  and dsheroh Do not use prepared statements.

I was waiting for that... 

At least at the beginning. Get the hang of pure raw SQL syntax first.

I would argue that

Code: [Select]

"SELECT a, b, c FROM my_table WHERE d = ?" is closer to "pure raw SQL syntax" than

Code: [Select]

"SELECT a, b, c FROM my_table WHERE d = '" . $some_var . "'"The former is defined entirely by the SQL standard (and the db engine's implementation of same...), while the latter mixes elements of the host language (e.g., PHP) into the middle of the SQL string.

It's actually part of the reason I prefer prepared statements aesthetically - I don't have interpolations, concatenations, or sprintf formatting codes wedged into the middle of my SQL, only SQL placeholders.

[Topazan]

I'm inclined to prefer prepared statements in terms of aesthetics and readability. 

Performance-wise, I'm not too concerned since hopefully my first app isn't going to push my server to the limits.  We'll see, I guess.

Unfortunately, I can't find a HaXe library that interfaces with mysqli.  It does have library for something called "SPOD" though, which seems like it may be exactly what I need.  I'll try it out.

I have one more question while I'm here.  How do you handle timed events?  Do you just check if the timer's up whenever the user does, or do you have a program running in the background that monitors timers?

Thanks everyone for your help.

[JGadrow]

Either way could be used depending upon your available resources. I like the idea of creating items that are processed via cron run (or scheduled task on windows) so that the load is spread out and balanced a bit better than if, say, you processed with "just in time" logic.

You should also be checking with JIT logic even if you do setup a cron task. That way, relevant events are processed immediately prior to a need because, odds are, at least some of those are going to fall between cron runs. The cron jobs just help to normalize the resource usage.

[Delifisek]

Using file model is best.Combining with opcode cache bringing best performance...

My projects are multilingual maybe more than 5 years. Once upon a time, I put my language keys in sql. It just wasting your resources... Putting lang files in php array format best for editing, caching performance etc.

Sample usage

lang.en.php

<?php
$lang['this'] = 'this';
$lang['that'] = 'that';
?>

lang.tr.php

<?php
$lang['this'] = 'bu';
$lang['that'] = 'su';
?>

page.php
<?php
requre('lang.'.$language.'.php');

echo "<strong>".$lang['this']."</strong>";

[Topazan]

JGadrow- Makes sense.  What kind of script would the cron event trigger?

Delifisek-  Thanks, but I think you misunderstood me. I was talking about html, php, and sql, not human languages.  Still, thanks for the tip, it may come in handy in the future.

[Chris]

I disagree with both JGadrow  and dsheroh Do not use prepared statements.

I was waiting for that... 

I couldn't bear to disappoint you

I'm inclined to prefer prepared statements in terms of aesthetics and readability.  

Traitor

I have one more question while I'm here.  How do you handle timed events?  Do you just check if the timer's up whenever the user does, or do you have a program running in the background that monitors timers?

Two options (actually sometimes it might be worth tho use both in the same game):
1) cronjobs (start with this one, more flexible)
2) per player triggerd update

[JGadrow]

JGadrow- Makes sense.  What kind of script would the cron event trigger?

As an example from the world of business logic, Drupal just has a single page that is called at the time of cron (/cron.php). The way the process works is that modules wanting to do something whenever the cron job is run register their intent by creating a function that's name ends with _cron and having either no parameters or all parameters are optional.

Of course, the way it works is up to you. I've encountered disadvantages using that system (say, if you want something to run repetitively, but not every time the cron job is run) but, in general, it's kind of nice to have a singular process that some of your objects just hook into.

[Topazan]

I'm inclined to prefer prepared statements in terms of aesthetics and readability.  

Traitor

Hey, talk about performance all you want, but you don't actually think those concatenated string statements look good, do you?

Besides, like I said, I've decided to try this SPOD thing anyways.  I don't know how that is performance wise.

As an example from the world of business logic, Drupal just has a single page that is called at the time of cron (/cron.php). The way the process works is that modules wanting to do something whenever the cron job is run register their intent by creating a function that's name ends with _cron and having either no parameters or all parameters are optional.

Hmm, interesting.  Didn't know you could do that.

[Topazan]

I'm inclined to prefer prepared statements in terms of aesthetics and readability.  

Traitor

Hey, talk about performance all you want, but you don't actually think those concatenated string statements look good, do you?

Besides, like I said, I've decided to try this SPOD thing anyways.  I don't know how that is performance wise.

As an example from the world of business logic, Drupal just has a single page that is called at the time of cron (/cron.php). The way the process works is that modules wanting to do something whenever the cron job is run register their intent by creating a function that's name ends with _cron and having either no parameters or all parameters are optional.

Hmm, interesting.  Didn't know you could do that.
[/quote]

EDIT:  One more question.  Does anyone have an example of an SQL injection attack that can bypass mysql_real_escape_string(), or is it just theoretical?

[Chris]

I'm inclined to prefer prepared statements in terms of aesthetics and readability.  

Traitor

Hey, talk about performance all you want, but you don't actually think those concatenated string statements look good, do you?

Actually... I do this beacuse for me raw sql looks much more beautiful, not because of performance...

One more question.  Does anyone have an example of an SQL injection attack that can bypass mysql_real_escape_string(), or is it just theoretical?

Integers.
Strings are secured because "aaaa='$var'", numbers not always because "aaaa=$var". You could add $var="; DROP DATABASE;". To counter you just have to always assure conversion of ints to ints.

There are also some dirty tricks with non english charsets (but I think real_escape_string catches them).

[dsheroh]

I have one more question while I'm here.  How do you handle timed events?  Do you just check if the timer's up whenever the user does, or do you have a program running in the background that monitors timers?

Yes.

Less cryptically, there are three common methods for handling this sort of thing and I use all three, depending on the type of event:

#1:  Lazy event processing ("check if the timer's up whenever the user does") - update the information each time somebody looks at it.  This is generally the most efficient way of handling things that are rarely referenced, since they won't be getting recalculated when nobody would notice anyhow, or which need to be completely up-to-the-second, since they're getting calculated immediately before display.  It also has the advantage of always being available, no matter where your code is hosted, since it's handled entirely within your normal application code.  Its disadvantages are that it can get complex for events which depend on other events (you can't see the results of your attack on a target if three other people have attacks against that same target that have "already taken place", but their results haven't been calculated yet) and it doesn't work for events which the user should be proactively notified of (e.g., sending email telling them that their production queue is empty).

#2:  Cron jobs ("a program running in the background that monitors timers") - set up a schedule of times when code should be run by the operating system's task scheduling service.  This is ideal for regularly-occurring events, such as a game which processes one turn per day; just set up a cron job to run process_turn at 12:07 each day and you're golden.  In my experience, most web hosts allow you to set up cron jobs, so it's still pretty portable, but some don't.  You could also get something more fluid by setting up a cron job to run every minute which checks whether there's anything to do and exits if not, but, personally, I don't like to use them for something that frequent unless absolutely necessary due to the overhead of starting up the process each time it's run.  Since this is essentially doing batch processing of everything that needs to be done when the job runs, it can more readily handle events with complex interdependencies and, since it runs independently, it can also do user notifications in (more-or-less) real time.

#3:  Daemons - programs which run constantly on the server, managing events by whatever rules you've set up.  Daemons can do everything that cron jobs can, but are more suited to handling frequent actions because it doesn't have to shut down and start back up all the time (it stays running and just sleeps when it runs out of things to do) and because you can have a daemon check for events to process as frequently as you like (and can afford the CPU time for) - cron doesn't let you schedule a job to run more often than once a minute.  Most web hosts, however, do not allow you to run daemons (which they usually refer to as "long-running processes" or similar), so this option is generally not available unless you're running your own server.

My general preference is to mostly use a mix of lazy calculation and daemons.  While I do use cron jobs for some Real Life applications, I tend to prefer my games to have more fluid scheduling than cron jobs are entirely suited for.

EDIT:  One more question.  Does anyone have an example of an SQL injection attack that can bypass mysql_real_escape_string(), or is it just theoretical?

I'm not aware of any, but, then, I don't use PHP on a regular basis, so I'm not intimately familiar with any quirks of mysql_real_escape_string.

But, even if it is purely theoretical, that's beside the point.

If you rely on escaping for protection against SQL injection, then you have to remember to escape every string that might get inserted into a query, every time - that's the cognitive overhead that I mentioned earlier.  You have to constantly be aware of database-related issues even when in code which is completely unrelated to the database because the task of preventing SQL injection is (potentially) spread throughout your entire codebase.

In contrast, when using SQL placeholders, you just put the placeholder into your query when you prepare it and you're done.  You don't have to do anything special to the data that gets used when you execute the query, so you can go about the rest of your coding without needing to think about database security because that's handled entirely within the code which talks to the database.

It's not just about the difference between escaping offering excellent protection and placeholders giving absolute protection.  Even if both give equal protection when used correctly, escaping requires you to remember to do it, every time.  If you forget, you lose its protection.  With placeholders, you do it once and you can forget about it afterward because there's nothing more you need to remember to do.

(And the reference to providing "protection when used correctly" sent my mind off on a contraception tangent, which led to escaping = condoms (must be used correctly every time; easy to use incorrectly if you don't know what you're doing; wraps up your data in a protective layer) and placeholders = vasectomy (do it once and you never have to think about it again; naked data stored without modification)...)

[JGadrow]

placeholders = vasectomy (do it once and you never have to think about it again; naked data stored without modification)

Technically speaking, with a vasectomy, your "naked data" has been modified.

[dsheroh]

placeholders = vasectomy (do it once and you never have to think about it again; naked data stored without modification)

Technically speaking, with a vasectomy, your "naked data" has been modified.

Fair enough.  I was just looking for something to contrast with "wrapped in a protective layer". 

[Topazan]

I think I understand now, but placeholders may not be an option with the api I'm using now.  Oh well, if I find I need more protection than escaping can give me, maybe I'll try using pack() on the strings.  Thanks for all the help.