Raised This Month: $ Target: $400
 0% 

Preventing SQL from injection


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Lukass
Junior Member
Join Date: Dec 2008
Old 01-08-2009 , 14:47   Preventing SQL from injection
Reply With Quote #1

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
Lukass is offline
Bad_Bud
Senior Member
Join Date: Oct 2006
Location: The internet
Old 01-08-2009 , 14:50   Re: Preventing SQL from injection
Reply With Quote #2

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.
__________________

Last edited by Bad_Bud; 01-08-2009 at 14:58.
Bad_Bud is offline
Lukass
Junior Member
Join Date: Dec 2008
Old 01-08-2009 , 14:56   Re: Preventing SQL from injection
Reply With Quote #3

Yes, i know that, but i need user name to work.
Lukass is offline
Bad_Bud
Senior Member
Join Date: Oct 2006
Location: The internet
Old 01-08-2009 , 15:02   Re: Preventing SQL from injection
Reply With Quote #4

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)
__________________

Last edited by Bad_Bud; 01-08-2009 at 15:05.
Bad_Bud is offline
Lukass
Junior Member
Join Date: Dec 2008
Old 01-08-2009 , 15:09   Re: Preventing SQL from injection
Reply With Quote #5

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 " ` ".
Lukass is offline
Bad_Bud
Senior Member
Join Date: Oct 2006
Location: The internet
Old 01-08-2009 , 15:11   Re: Preventing SQL from injection
Reply With Quote #6

Odd, but you may still want to remove those apostrophes. Any reason why you have two different kinds of single quotes on your query?
__________________
Bad_Bud is offline
Lukass
Junior Member
Join Date: Dec 2008
Old 01-08-2009 , 15:37   Re: Preventing SQL from injection
Reply With Quote #7

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.
Lukass is offline
danielkza
AMX Mod X Plugin Approver
Join Date: May 2007
Location: São Paulo - Brasil
Old 01-08-2009 , 16:18   Re: Preventing SQL from injection
Reply With Quote #8

Quote:
Originally Posted by Lukass View Post
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.
__________________

Community / No support through PM
danielkza is offline
Bad_Bud
Senior Member
Join Date: Oct 2006
Location: The internet
Old 01-08-2009 , 20:52   Re: Preventing SQL from injection
Reply With Quote #9

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.
__________________
Bad_Bud is offline
YamiKaitou
Has a lovely bunch of coconuts
Join Date: Apr 2006
Location: Texas
Old 01-08-2009 , 20:57   Re: Preventing SQL from injection
Reply With Quote #10

Quote:
Originally Posted by Bad_Bud View Post
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
__________________
ProjectYami Laboratories

I do not browse the forums regularly anymore. If you need me for anything (asking questions or anything else), then PM me (be descriptive in your PM, message containing only a link to a thread will be ignored).
YamiKaitou is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


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


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