AAC MYAAC, database fetching

[Sprrw]

Hola amigos!
Recently I've been learning a lot of PHP and I've started creating my own templates!
So I've recently also started creating my own kind of PHP scripts.
And I was wondering how to keep myself safe. I mean does this line make sense?

$news_db = $db->query('SELECT `title`, `body` ,`category`, `date`, `player_id`, `id`, `article_image` FROM `myaac_news` ORDER BY `date` DESC LIMIT 1');

Im asking cuz if I check the MyAAC files they have a lot of like ip checks etc before fetching database data. Should I always have like those kind of checks before fetching something from my DB?
Noob question maybe but better be safe than sorry
Thanks for reading! <3 Happy valentines

 

[Rexxar]

Something that comes to mind is cacheing. You should try to avoid querying the database on every page load, as someone could easly DDOS your database by having bots refresh the page over and over again. This is probably part of all those "IP checks" you're talking about.

Oh and if it's a query that in any way modifies character/account data or fetches otherwise private information, always always always make sure it's protected from XSS. Otherwise anyone with enough time and guessing ability could start messing with other people's characters, or give themselves admin powers and all kinds of scary stuff.

 

[Sprrw]

Something that comes to mind is cacheing. You should try to avoid querying the database on every page load, as someone could easly DDOS your database by having bots refresh the page over and over again. This is probably part of all those "IP checks" you're talking about.

Oh and if it's a query that in any way modifies character/account data or fetches otherwise private information, always always always make sure it's protected from XSS. Otherwise anyone with enough time and guessing ability could start messing with other people's characters, or give themselves admin powers and all kinds of scary stuff.

Ohh okay, thanks a lot for your answer m8!
How would I go about fetching db info tho? I mean how would I cache the info so they wont have to load it every time?

BTW. Would fetching all account ids count as private information? I mean there is really nothing linking any accounts to the ids thats available in something like

$registeredPlayers = $db->query('SELECT `id` FROM `accounts` ORDER BY `id`')

 

[slaw]

About DDOS: this should be carried by your web server, not the script itself. Modules exist for both apache2 and nginx or any other server that limits requests per second, so it won't be a problem that you do a few queries. Or use services like CloudFlare.

About the first question: the query you posted is safe, because it doesn't contain user data. The problem comes when the user can enter anything, and then you do query. This is called SQL injection. This is the biggest danger your script can be affected by.

1) To prevent this (in MyAAC), we can use $db->quote() function. This way, if you quote every value user gives, there is no way for SQL injection.

Example, we fetch all data from players table, and user provides a name:

$name = $_REQUEST['name']; // user enters name in form
$query = $db->query('SELECT * FROM players WHERE name = ' . $db->quote($name));

This way we got a safe query.

2) Second option is to use PHP PDO prepared statement:

$coins = $_REQUEST['coins']; // user enters coins in form
$statement = $db->prepare('UPDATE `accounts` SET `coins` = `coins` + :coins');
$statement->execute(['coins' => $coins]);

This will add every account x points.

3) Third option, is to use other functions, that are there in MyAAC. They are a bit limited, tho.

$name = $_REQUEST['name']; // user enters name in form

$query = $db->select('players', ['name' => $name]);
if ($query === false) {
    echo 'no results';
}
else {
    var_dump($query);
}

It's up to you what you choose

 

[Sprrw]

About DDOS: this should be carried by your web server, not the script itself. Modules exist for both apache2 and nginx or any other server that limits requests per second, so it won't be a problem that you do a few queries. Or use services like CloudFlare.

About the first question: the query you posted is safe, because it doesn't contain user data. The problem comes when the user can enter anything, and then you do query. This is called SQL injection. This is the biggest danger your script can be affected by.

1) To prevent this (in MyAAC), we can use $db->quote() function. This way, if you quote every value user gives, there is no way for SQL injection.

Example, we fetch all data from players table, and user provides a name:

$name = $_REQUEST['name']; // user enters name in form
$query = $db->query('SELECT * FROM players WHERE name = ' . $db->quote($name));

This way we got a safe query.

2) Second option is to use PHP PDO prepared statement:

$coins = $_REQUEST['coins']; // user enters coins in form
$statement = $db->prepare('UPDATE `accounts` SET `coins` = `coins` + :coins');
$statement->execute(['coins' => $coins]);

This will add every account x points.

3) Third option, is to use other functions, that are there in MyAAC. They are a bit limited, tho.

$name = $_REQUEST['name']; // user enters name in form

$query = $db->select('players', ['name' => $name]);
if ($query === false) {
    echo 'no results';
}
else {
    var_dump($query);
}

It's up to you what you choose

Thanks a lot for your answer! Its getting quite late so Ima test some out tmrw and may get back to you if I got more questions. But thanks a lot. This was very helpful! <3