It is currently Mon Dec 22, 2014 1:39 am

All times are UTC - 7 hours




Post new topic Reply to topic  [ 14 posts ] 
Author Message
 Post subject: NES Cart DB 2.0
PostPosted: Tue Feb 10, 2009 4:50 pm 
Offline

Joined: Wed Jul 13, 2005 3:14 pm
Posts: 338
Well the new site is finally up and running. I will post a run down of the changes on the site in a little bit, but for now, feel free to snoop around!

http://bootgod.dyndns.org:7777/home.php

Please let me know if you find any bugs or anything thing you'd like to see there. I'd like to hear overall opinions about the site as well, good or bad!

Thanks for looking!


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 02, 2009 6:39 pm 
Offline
User avatar

Joined: Thu Jan 03, 2008 1:48 pm
Posts: 345
Everything's looking mighty fine with the new database. I'm excited to start working with the new software.

I'm wondering if you can include all of the necessary utilities for dumping from previously unknown boards like in the other clients (ex: Bankwatch and Microbug) for the next release. This way we wouldn't have to go back and forth.

Also when successful plugins are made they should be able to be uploaded to your database for other users to use.

Also it would be quite convenient to include RAM updating plugins for the LPT CopyNES users so PowerPak ROMs could be updated, etc...

Looking forward to dumping some carts from newer regions not currently in the database. :)


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 02, 2009 6:44 pm 
Offline
User avatar

Joined: Tue Jun 24, 2008 8:38 pm
Posts: 1339
Location: Fukuoka, Japan
This is a really nice site. I think you should consider someday to make a read-only mirror of it, just it case you need to do maintenance or something.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 06, 2009 5:49 am 
Offline

Joined: Tue Aug 07, 2007 10:28 am
Posts: 81
Anyone else having trouble downloading the database?

I haven't been able to connect since the update. Also, I notice I can't click the software link since the update.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 06, 2009 8:28 am 
Offline
User avatar

Joined: Thu Jan 03, 2008 1:48 pm
Posts: 345
I'm pretty sure he has that stuff offline for a reason; probably due to him not wanting inaccuracies or incompatibilities in the database. He's making a new client software.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 07, 2009 8:21 am 
Offline
Site Admin
User avatar

Joined: Sun Sep 19, 2004 9:28 pm
Posts: 1982
Location: Mountain View, CA, USA
A technicality: he hasn't taken the server (specifically the MySQL server) offline, but he *has* removed or done something with the MySQL DB called "cartdb". For all we know, he could've done "DROP DATABASE cartdb", who knows. I'm not sure how that has anything to do with him "working on the client".

If he wants to take the DB offline for whatever purpose, great -- then shut off mysql, or add some hooks in the PHP code to check for a file or global variable that defines whether or not it should even bother with the SQL queries in the first place.

Spitting back a bunch of MySQL errors at the end-user doesn't give people the impression "things are being worked on". It gives people the impression something is broken (in this case, a missing MySQL DB).

Also, I hope someone takes the time to examine the PHP code for any SQL injection attacks.

EDIT:

Seems most of the things I tried failed due to use of mysql_real_escape_string(). What a worthless function to boot -- a proper DBI implementation that offers placeholders would remove such useless functions. Requiring PEAR or requiring mysqli (which requires SPL) just to get proper prepare() and placeholder support is such a load. PHP 6 already please!

Anyway, there are some bugs:

The Section Browser dumps the section character you want directly from $_GET into the query. For example, visit:

http://bootgod.dyndns.org:7777/search.p ... se=%25apes

And you'll end up with this subquery:

Code:
... SELECT c.cartid FROM tbl_carts c,tbl_info i WHERE c.active = 'Yes' AND c.infoid = i.infoid AND i.title LIKE '%apes%' ...


This piece of the code needs to be changed to only permit certain characters in the $_GET field. [A-Za-z0-9_-] sounds like a good list.

Also, in the Advanced Search, probably pertaining to magic_quotes_gpc being turned on. All PHP configurations at this point in time should have this disabled globally and code developed to assume it's off -- the feature has been removed entirely in PHP 6 due to the security risks of it.

Repeating the bug is simple. Advanced Search, Game Title contains: 'FOO', click search, then click back. Be sure to include the apostrophes in 'FOO'.

I wonder how many Google results there are for the string "\'". Good 'ole PHP...


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 07, 2009 2:55 pm 
Offline
User avatar

Joined: Sun Sep 19, 2004 11:12 pm
Posts: 12904
Location: NE Indiana, USA (NTSC)
koitsu wrote:
Seems most of the things I tried failed due to use of mysql_real_escape_string(). What a worthless function to boot -- a proper DBI implementation that offers placeholders would remove such useless functions. Requiring PEAR or requiring mysqli (which requires SPL) just to get proper prepare() and placeholder support is such a load.

Placeholders are nice if you know in advance how many ?'s you're going to put in a query. But as I learned on my current project, some searches need a variable number of ?'s, each of which is present if and only if a specific kind of term is present in the query. For example, do you want to limit or not limit the search to a given publisher, or to a given genre? The mysqli module's $stmt->bind_param made it such a pain to pass a variable number of arguments that I gave up and used $db->escape_string.

Quote:
The Section Browser dumps the section character you want directly from $_GET into the query. For example, visit:

http://bootgod.dyndns.org:7777/search.p ... se=%25apes

And you'll end up with this subquery:

Code:
... SELECT c.cartid FROM tbl_carts c,tbl_info i WHERE c.active = 'Yes' AND c.infoid = i.infoid AND i.title LIKE '%apes%' ...


This piece of the code needs to be changed to only permit certain characters in the $_GET field. [A-Za-z0-9_-] sounds like a good list.

That or use the "x LIKE y ESCAPE z" syntax, and escape all '%' and '_' characters in the input.

Quote:
Also, in the Advanced Search, probably pertaining to magic_quotes_gpc being turned on. All PHP configurations at this point in time should have this disabled globally

Go Daddy shared hosting forces MQ on. It's on when you sign up, and you can't turn it off in php.ini (no effect) or .htaccess (500 Internal Server Error if i remember correctly). I originally developed my current project with no MQ and had to write a stripslashes loop to work around this when developing my employer's online catalog. Once we outgrew shared hosting and moved to a virtual dedicated server, I breathed a sigh of relief that I didn't have to deal with magic drugs anymore.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 08, 2009 7:06 pm 
Offline

Joined: Wed Jul 13, 2005 3:14 pm
Posts: 338
A few days ago, I moved the system to a new PC, also moved to a new version of MySQL and I apparently was in too much of a hurry because I screwed a bunch of the db privileges up in the process. I was trying to get it back online as quickly as possible and as such didn't test it thoroughly. I just now fixed a problem with the browse section that made it useless for the past week.

koitsu: Thanks for the insight, in response to some of the things brought up:

Quote:
Spitting back a bunch of MySQL errors at the end-user doesn't give people the impression "things are being worked on". It gives people the impression something is broken


Error messages regarding search queries are being output on purpose to make it easy for people to copy & paste the error to tell me about it. Can easily be shut off if I want. However, this is the only place it should be doing this, if you know of others, please let me know.

Quote:
Seems most of the things I tried failed due to use of mysql_real_escape_string()...


I hate the implementation of this function as well (it's even more of a pain in the C API!) but are you implying I should be doing something else?

Quote:
The Section Browser dumps the section character you want directly from $_GET into the query.


I can't seem to reproduce this error with the example given, but yes if you enter some nonsense character in there it can give you an error message. Certainly can be fixed, but hardly a pressing matter IMHO, as you'd have to intentionally be doing this.

Quote:
Also, in the Advanced Search, probably pertaining to magic_quotes_gpc being turned on. All PHP configurations at this point in time should have this disabled globally and code developed to assume it's off -- the feature has been removed entirely in PHP 6 due to the security risks of it.

Yes magic quotes are enabled and I know that is a bad thing. I guess if they will not be available in the next major version of PHP I will have no choice but to stop using that 'feature' :)


2600 wrote:
Anyone else having trouble downloading the database?

I haven't been able to connect since the update. Also, I notice I can't click the software link since the update.


Yes unfortunately the updated software is still unavailable. I hope to have things ready ASAP. Also the DB download function is no longer going to be part of the software and will be integrated into the website instead, should be a better experience for all.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 09, 2009 6:33 am 
Offline

Joined: Tue Aug 07, 2007 10:28 am
Posts: 81
BootGod wrote:

2600 wrote:
Anyone else having trouble downloading the database?

I haven't been able to connect since the update. Also, I notice I can't click the software link since the update.


Yes unfortunately the updated software is still unavailable. I hope to have things ready ASAP. Also the DB download function is no longer going to be part of the software and will be integrated into the website instead, should be a better experience for all.


Thanks for the update. Thought I was doing something wrong. Any chance of being able to download the other things from your site? I'd like to be able to lookup something in the database and be able to see the PCB pics, etc even if I have to write my own app to do it.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 09, 2009 7:17 am 
Offline
User avatar

Joined: Sun Sep 19, 2004 11:12 pm
Posts: 12904
Location: NE Indiana, USA (NTSC)
BootGod wrote:
Error messages regarding search queries are being output on purpose to make it easy for people to copy & paste the error to tell me about it.

In that case, a "Please report this error message to BootGod" might have clarified.

Quote:
Quote:
Seems most of the things I tried failed due to use of mysql_real_escape_string()...

I hate the implementation of this function as well (it's even more of a pain in the C API!) but are you implying I should be doing something else?

The mysqli extension has prepared statements. Each ? is a placeholder where the SQL parser expects a parameter, and then every time you execute the prepared statement, it automatically real_escapes and quotes the data before plugging it into the SQL. If your queries are fill-in-the-blank like this, with a fixed set of args, it's easier to be sure you're using SQL safely.
Code:
$db->prepare("SELECT userid FROM users WHERE username = ?");
$db->bind_param('s', $username);
$db->execute();
$db->bind_result($userid);
if ($db->fetch()) {
  // we have a valid username. now put up the password form.
} else {
  failPage('User '.htmlspecialchars($username).' not found.');
}

The drawback comes from search forms with numerous options that the user can specify or not. For these, you pretty much have to concatenate a WHERE clause based on what the user has selected.

Quote:
Quote:
The Section Browser dumps the section character you want directly from $_GET into the query.

I can't seem to reproduce this error with the example given, but yes if you enter some nonsense character in there it can give you an error message. Certainly can be fixed, but hardly a pressing matter IMHO, as you'd have to intentionally be doing this.

Computer criminals who want to vandalize your database would intentionally be doing this.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 09, 2009 9:18 am 
Offline

Joined: Wed Jul 13, 2005 3:14 pm
Posts: 338
2600 wrote:
Any chance of being able to download the other things from your site? I'd like to be able to lookup something in the database and be able to see the PCB pics, etc even if I have to write my own app to do it.

Being able to download the original source images will probably be an option in the future.

This probably isn't exactly what you had in mind, but you can have PCB scans show up in the search results using the custom field selection in advanced search. The current results page layout isn't particularly well suited for images, but it works for now.

tepples wrote:
BootGod wrote:
Error messages regarding search queries are being output on purpose to make it easy for people to copy & paste the error to tell me about it.

In that case, a "Please report this error message to BootGod" might have clarified.

Good point. I suppose what I could do is just log the error and just let the user know there has been an error, but they wouldn't need to do anything. Only problem with that is I tend to neglect looking at logs :|

tepples wrote:
Computer criminals who want to vandalize your database would intentionally be doing this.
True, but who would want to do that! :P But seriously there are much bigger and more damaging security holes. The system is backed up weekly, so in the event of a douchebag with nothing better to do than trash the DB, it wouldn't be the end of the world.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 09, 2009 4:57 pm 
Offline
User avatar

Joined: Thu Jan 03, 2008 1:48 pm
Posts: 345
Thanks for the responses to the other posts, BootGod.

Mind giving my post close to the top a look and some insight? :)


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 09, 2009 6:13 pm 
Offline

Joined: Wed Jul 13, 2005 3:14 pm
Posts: 338
B00daW wrote:
Thanks for the responses to the other posts, BootGod.

Mind giving my post close to the top a look and some insight? :)


Heh, sorry didn't mean to leave you out :)

I never really intended the program to be a full-blown client that supports all features of the main client. I agree it is a pain though switching between the 2 because the driver will not allow both to be open at once. It's a possibility down the road though. RAM cart functionality I've considered in the past and probably will add support for it eventually.

I was going to add an upload form to the plugin page on the website, but the only thing that stopped me is was the question of what do when a user submits a newer version of a plugin? I'd rather there only be 1 version of each. I guess I could have the form there but not have them officially added until they have been reviewed.

And you can enter new regions now as well :)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Apr 10, 2009 9:26 am 
Offline
User avatar

Joined: Thu Jan 03, 2008 1:48 pm
Posts: 345
That all sounds fine. Regarding the plugins, I get you.

Would be nice to have an area to submit new plugins (dropbox, etc...) and a forum to speak regarding them. This thread itself might even do.

Well, it seems you got a good idea and plan for the utility. :D


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 14 posts ] 

All times are UTC - 7 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group