[Blacklite]

Quote:

Originally Posted by ****** Two things which could have been made much clearer: 1) This is NOT a MySQL attack, it is an SQL attack, this means it works on SQLite, PostgreSQL, MSSQL and others beside MySQL.

You are 100% correct, although the topic was mainly aimed at MySQL (I referenced MySQL functions and its user system).

Quote:

Originally Posted by ****** 2) If you are running a server (or anything) on a user with full database modification privaledges then you deserve everything you get!

Can't disagree there!

Quote:

Originally Posted by ****** On an OS you should have a special user account for managing the database - creating and modifying tables etc, and another one with far less privaledges for day-to-day operations. Set one up with only "INSERT", "SELECT", "UPDATE" and MAYBE "DELETE" permissions, though you don't really need "DELETE" either on a standard user as you can just use a field to indicate "removed" and add "removed=0" to your "WHERE"s.

I personally use the root MySQL account to set up all databases, and then make a different user for each database with INSERT/SELECT/UPDATE/DELETE permissions. I'm not worried about deleting records, as I have a script that runs daily to optimize all databases, and I make regular backups of all databases too.

The removed=1 approach can become very useful, especially when dealing with users deleting their own blog comments or forum posts etc. For administrative data, I'm not so worried as abusive admins can be demoted and lost data restored quite easily.

Quote:

Originally Posted by ****** This structure will ensure the least chance of someone breaking your database, however it is still possible to get data they shouldn't or modify accounts they shouldn't. Frankly I'd recommend one user for querying the database and a separate user for modifying it.

This is quite easy to set up. The best approach is to make some functions (put them in a class if you have such luxuries!) with names like mysql_delete, mysql_select, mysql_insert etc and then you can manage which connections are used from within those functions. Example:

pawn Code:

new MySQL:MYSQL_SELECT, MySQL:MYSQL_UPDATE, MySQL:MYSQL_INSERT, MySQL:MYSQL_DELETE;

public OnGameModeInit() {
    MYSQL_SELECT = mysql_connect(MYSQL_HOST, MYSQL_L1_USER, MYSQL_L1_PASS, MYSQL_DB);
    MYSQL_INSERT = MYSQL_UPDATE = mysql_connect(MYSQL_HOST, MYSQL_L2_USER, MYSQL_L2_PASS, MYSQL_DB);
    MYSQL_DELETE = mysql_connect(MYSQL_HOST, MYSQL_L3_USER, MYSQL_L3_PASS, MYSQL_DB);
}

stock mysql_select(query[]) {
    mysql_query(query, MYSQL_SELECT);
}

stock mysql_update(query[]) {
    mysql_query(query, MYSQL_UPDATE);
}

stock mysql_insert(query[]) {
    mysql_query(query, MYSQL_INSERT);
}

stock mysql_delete(query[]) {
    mysql_query(query, MYSQL_DELETE);
}

Of course this is just a rough example... But, once you get to this level of organization, you can start to develop functions like this:

pawn Code:

stock mysql_insert(table[], {Float,_}:...) {
    // blah blah blah
}

// in some function...
mysql_insert("users", "username", "Blacklite", "password", "some hash", "kills", "666");

With this method, you can also sanitize each value before it goes into the database - this entirely eliminates the possibility of SQL injection, if used consistently. In fact, these functions should really be in the MySQL Plugins - maybe a request could be made?

Meh, thanks for your input anyway