AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   Preventing SQL from injection (https://forums.alliedmods.net/showthread.php?t=83370)

Lukass 01-08-2009 14:47

Preventing SQL from injection
 
Hi ppl,

I have one MySQL query:
Code:

format(CheckQuery, 254, "SELECT `id` FROM `users` WHERE `nick`='%s' AND `password`='%s'", user_name,password)
It contains user's name, and it can be SQL injection's result. How can i prevent it ? If i set my name to "; 'OR 'x'='x", my server's console says:
Code:

01/08/2009 - 21:39:45: [AMXX] Plugin ("sql_vip.amxx") is setting itself as failed.
L 01/08/2009 - 21:39:45: [AMXX] Plugin says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'='x' AND `password`='lopas'' at line 1
L 01/08/2009 - 21:39:45: [AMXX] Run time error 1 (plugin "sql_vip.amxx") - forced exit
Ignoring custom decal from ; OR 'x'='x
L 01/08/2009 - 21:39:45: "; OR 'x'='x<1><STEAM_ID_PENDING><>" entered the game


Bad_Bud 01-08-2009 14:50

Re: Preventing SQL from injection
 
If you store by their steam id and not their name, you could be guaranteed that this wouldn't happen, unless they set their password to have one of the same characters (which may cause this same odd problem for you), but you can check for that when they try to set a password.

PHP Code:

static SteamID[37]
get_user_authid(id,SteamID[id],36

Edit: I'm using mySQL and I ran a query searching for ";" in a string field, and it returned a value just fine. Perhaps either you are doing something wrong with the user's name before you make this call, or you are using a version of SQL that doesn't like semicolons. Either way, I still suggest using their steam id if it will work fine for whatever your goal is.

Output exactly what you're passing into the query using a print_chat or print_console before you run the query, and post exactly what's making it in there.

Lukass 01-08-2009 14:56

Re: Preventing SQL from injection
 
Yes, i know that, but i need user name to work.

Bad_Bud 01-08-2009 15:02

Re: Preventing SQL from injection
 
Code:

format(CheckQuery, 254, "SELECT `id` FROM `users` WHERE `nick`='%s' AND `password`='%s'", user_name,password)
Uh, just noticed you have single quotes (or rather apostrophes) around all of your fields. Remove those. It should be something like:

Code:

format(CheckQuery, 254, "SELECT id FROM users WHERE nick='%s' AND password='%s'", user_name,password)
http://perkbrian.com/forums/Uploads/QueryFail.PNG

Lukass 01-08-2009 15:09

Re: Preventing SQL from injection
 
I tried to do this:
Code:

    replace_all(user_name, 99, "'", "\'")
And it looks to work.

//EDIT:

Thease quotes aren't apostrophes, it`s "back quotes". not " ' ", but " ` ".

Bad_Bud 01-08-2009 15:11

Re: Preventing SQL from injection
 
Odd, but you may still want to remove those apostrophes. Any reason why you have two different kinds of single quotes on your query?

Lukass 01-08-2009 15:37

Re: Preventing SQL from injection
 
Try to run query:
Code:

SELECT * FROM db WHERE select='blah';
It won't work, but if we use this:
Code:

SELECT * FROM db WHERE `select`='blah';
It will work perfectly. I know i know, maybe in my query it isn't necassary, i will remove that.

danielkza 01-08-2009 16:18

Re: Preventing SQL from injection
 
Quote:

Originally Posted by Lukass (Post 740602)
Try to run query:
Code:

SELECT * FROM db WHERE select='blah';
It won't work, but if we use this:
Code:

SELECT * FROM db WHERE `select`='blah';
It will work perfectly. I know i know, maybe in my query it isn't necassary, i will remove that.

Maybe because SELECT is a reserved word? You should really avoid using field names that are/resemble statements.

Bad_Bud 01-08-2009 20:52

Re: Preventing SQL from injection
 
I still don't understand how/why you have single quotes on the values and apostrophes on the fields... unless it has something to do with non-American keyboards.

YamiKaitou 01-08-2009 20:57

Re: Preventing SQL from injection
 
Quote:

Originally Posted by Bad_Bud (Post 740776)
I still don't understand how/why you have single quotes on the values and apostrophes on the fields... unless it has something to do with non-American keyboards.

It is proper MySQL syntax


All times are GMT -4. The time now is 09:05.

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.