[Blacklite]

Hi

It seems there are a lot of people in this community who use MySQL, and are unaware of the dangers. MySQL is a fantastic data storage system, and it works very well, but if inputs are not sanitized correctly, your database can be easily attacked by anyone with simple knowledge of SQL language.

SQL injection can happen on any application which connects to an SQL database, including SA:MP, and it is up to you to make sure it doesn't happen to you.

For this article, we will assume that "text" is a string variable containing some user input, and "string" is a string which we will be using to store our query.

ALL user input must be filtered before using it in a MySQL query. Here is a query that is prone to SQL injection attacks:

pawn Code:

format(string, sizeof(string), "SELECT * FROM users WHERE username='%s' LIMIT 1", text);

This works fine with "safe" data. For example, if text = Blacklite , the query going to the database will look like this:

Code:

SELECT * FROM users WHERE username='Blacklite' LIMIT 1

However, if the user enters '; DROP TABLE users;-- , then the query processed by MySQL will look like this:

Code:

SELECT * FROM users WHERE username='';
DROP TABLE users;

The SQL above will actually delete the whole `users` table, if your MySQL user has the permission to do so!

To prevent this from happening, you need to use mysql_real_escape_string like so:

pawn Code:

mysql_real_escape_string(text, string);
format(string, sizeof(string), "SELECT * FROM users WHERE username='%s' LIMIT 1", string);

Now, safe queries will still work, but "dangerous" queries like the one above will be escaped, and will look like this:

Code:

SELECT * FROM users WHERE username='\'; DROP TABLE users;--' LIMIT 1

Note the backslash (\) that the mysql_real_escape_string function has inserted before the single quote (') to stop MySQL from recognizing it as SQL code, and instead just treating it as a literal character.

And it's now all safe :)

One last thing: when using the mysql_real_escape_string() function, make sure you're using it properly with the correct syntax. If you use it incorrectly, it won't filter your strings!

[Jochemd]

I got to bump this up since still too less people are not alert.

[[HiC]TheKiller]

This is pretty old ******. The delete statement wouldn't need to be used on a user table but if people are loading anything else such as car info or information that needs to be deleted afterwards. You could always make 2-3 different MySQL connections to the 2-3 different accounts but I'm sure that most people wouldn't do it because of the hassle.

This would be lovely if everyone was aware of SQL injections including SONY because most new servers have this issue .

[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

[Toni]

I'd like a little information about the actual user joining under a certain query name. Say if I joined a server that ran MySQL and did a username like 'DROP TABLE users;' it would actually drop the table?

"DROP TABLE users; has joined the server!"

[Blacklite]

Quote:

Originally Posted by Toni I'd like a little information about the actual user joining under a certain query name. Say if I joined a server that ran MySQL and did a username like 'DROP TABLE users;' it would actually drop the table? "DROP TABLE users; has joined the server!"

Not unless you had code like this:

pawn Code:

public OnPlayerConnect(playerid)
{
    new pName[MAX_PLAYER_NAME];
    GetPlayerName(playerid, pName, MAX_PLAYER_NAME);
    mysql_query(pName);
}

And even then, people can't use spaces in their usernames in SA:MP.

The main thing to be watching out for is things like house names, where you have the user entering string data into the database.

[Toni]

Quote:

Originally Posted by Blacklite Not unless you had code like this: pawn Code: public OnPlayerConnect(playerid) {     new pName[MAX_PLAYER_NAME];     GetPlayerName(playerid, pName, MAX_PLAYER_NAME);     mysql_query(pName); } And even then, people can't use spaces in their usernames in SA:MP. The main thing to be watching out for is things like house names, where you have the user entering string data into the database.

Ah I see, my mistake. Now I know how would be able query stuff!

[Hal]

This should be stickied, because sqli is super easy, and people need to protect themselves.

It states this clearly in the wiki , but I do not think that people understand how much damage it can cause...