Be a Supporter!

MySQL sanitization with regex

  • 458 Views
  • 14 Replies
New Topic Respond to this Topic
egg82
egg82
  • Member since: Jun. 24, 2006
  • Offline.
Forum Stats
Member
Level 05
Game Developer
MySQL sanitization with regex Sep. 30th, 2012 @ 02:26 PM Reply

I am connecting directly to a MySQL database (no PHP/ASP/etc) and realized that the methods i'm using don't have a way to sanitize input.

Connections to the database are secure despite being in an open-source project, so I do have reason to sanitize input.

so if any of you regex folks out there could help me (I am horrible with regex) it would be greatly appreciated. Here's what i've got:

public function san(input:String):String {
	return input.replace(/[^\\'][^\\"]/, "\'");
}

Programming stuffs (tutorials and extras)
PM me (instead of MintPaw) if you're confuzzled.
thank Skaren for the sig :P

BBS Signature
MSGhero
MSGhero
  • Member since: Dec. 15, 2010
  • Offline.
Forum Stats
Supporter
Level 16
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 02:38 PM Reply

At 9/30/12 02:26 PM, egg82 wrote: I am connecting directly to a MySQL database (no PHP/ASP/etc) and realized that the methods i'm using don't have a way to sanitize input.

Connections to the database are secure despite being in an open-source project, so I do have reason to sanitize input.

so if any of you regex folks out there could help me (I am horrible with regex) it would be greatly appreciated. Here's what i've got:

public function san(input:String):String {
return input.replace(/[^\\'][^\\"]/, "\'");
}

Could you give an example of what is supposed to happen? I've dealt with regexp before, but I have no idea what sanitizing input means lol.

egg82
egg82
  • Member since: Jun. 24, 2006
  • Offline.
Forum Stats
Member
Level 05
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 02:42 PM Reply

At 9/30/12 02:38 PM, MSGhero wrote: Could you give an example of what is supposed to happen? I've dealt with regexp before, but I have no idea what sanitizing input means lol.

oh, yeah, sorry :P

I want to escape single and double quotes, but want to avoid escaping already escaped quotes.

So if I get something like "Th\"is 'is' "a" te\'st" it should come out like "Th\"is \'is\' \"a\" te\'st"


Programming stuffs (tutorials and extras)
PM me (instead of MintPaw) if you're confuzzled.
thank Skaren for the sig :P

BBS Signature
MSGhero
MSGhero
  • Member since: Dec. 15, 2010
  • Offline.
Forum Stats
Supporter
Level 16
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 03:14 PM Reply

At 9/30/12 02:42 PM, egg82 wrote: oh, yeah, sorry :P

I want to escape single and double quotes, but want to avoid escaping already escaped quotes.

So if I get something like "Th\"is 'is' "a" te\'st" it should come out like "Th\"is \'is\' \"a\" te\'st"

2 problems:
1) That's not a valid string. The quotes around "a" don't cover the a.
2) I don't think you can actually get the \ from the string.

Or are you saying that's what tracing the string looks like, and the string actually comes in as "Th\\\"is 'is' \"a\" te\\'st"?

PSvils
PSvils
  • Member since: Feb. 3, 2010
  • Offline.
Forum Stats
Member
Level 01
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 03:22 PM Reply

At 9/30/12 02:26 PM, egg82 wrote: I am connecting directly to a MySQL database (no PHP/ASP/etc) and realized that the methods i'm using don't have a way to sanitize input.

Directly from Flash/AS3? First of all, why would you do this? Second of all, don't do this, it isn't secure at ALL.

egg82
egg82
  • Member since: Jun. 24, 2006
  • Offline.
Forum Stats
Member
Level 05
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 03:32 PM Reply

At 9/30/12 03:14 PM, MSGhero wrote: Or are you saying that's what tracing the string looks like, and the string actually comes in as "Th\\\"is 'is' \"a\" te\\'st"?

no, that's the string it would come in as.

so am I worrying over nothing, or is this not possible?

basically what i'm trying to avoid is this:

"SELECT `pass` FROM ACCOUNTS WHERE `user`='" + input + "'"
when input is: "' OR 1=1 OR `pass`='"
so the query would be: SELECT `pass` FROM ACCOUNTS WHERE `user`='' OR 1=1 OR `pass`=''
which would select the first user's password (the admin's)

so I just escape quotes and that doesn't happen:
SELECT `pass` FROM ACCOUNTS WHERE `user`='\' OR 1=1 OR `pass`=\''
which would just grab the user \' OR 1=1 OR `pass`=\' - which probably doesn't exist.


Programming stuffs (tutorials and extras)
PM me (instead of MintPaw) if you're confuzzled.
thank Skaren for the sig :P

BBS Signature
MSGhero
MSGhero
  • Member since: Dec. 15, 2010
  • Offline.
Forum Stats
Supporter
Level 16
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 03:38 PM Reply

At 9/30/12 03:32 PM, egg82 wrote: no, that's the string it would come in as.

so am I worrying over nothing, or is this not possible?

Not sure if it's possible to come in that way or not, but ""a"" isn't valid.

egg82
egg82
  • Member since: Jun. 24, 2006
  • Offline.
Forum Stats
Member
Level 05
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 03:38 PM Reply

At 9/30/12 03:22 PM, PSvils wrote: Directly from Flash/AS3? First of all, why would you do this? Second of all, don't do this, it isn't secure at ALL.

I am very aware of the repercussions of connecting directly to a database in a client application.

I am using Adobe AIR for a client/server setup for multiplayer games, and the server will have direct access to a MySQL database (internal or external) to keep waiting to a minimum - the database info is stored in a configuration file in a private folder, and no personal information is defaulted to the server.

I'm posting the source to github (committing when I make a big change or at the end of the day) if you would like to see how it works.

At this current moment i'm just getting things working, so the code's pretty messy. I'll clean it up after i'm done.


Programming stuffs (tutorials and extras)
PM me (instead of MintPaw) if you're confuzzled.
thank Skaren for the sig :P

BBS Signature
egg82
egg82
  • Member since: Jun. 24, 2006
  • Offline.
Forum Stats
Member
Level 05
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 03:40 PM Reply

At 9/30/12 03:38 PM, MSGhero wrote: Not sure if it's possible to come in that way or not, but ""a"" isn't valid.

no, but for sake of argument say the string is valid :P

double-post, sorry.


Programming stuffs (tutorials and extras)
PM me (instead of MintPaw) if you're confuzzled.
thank Skaren for the sig :P

BBS Signature
MSGhero
MSGhero
  • Member since: Dec. 15, 2010
  • Offline.
Forum Stats
Supporter
Level 16
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 04:19 PM Reply

At 9/30/12 03:40 PM, egg82 wrote: no, but for sake of argument say the string is valid :P

double-post, sorry.

For the sake of argument, let's assume it's valid because the escape chars are actually there :)

(/[^\\'][^\\"]/, "\'")

The double quote needs to go outside the [ ] or it gets treated like a single quote. And you need a | between the two cases. And replace only replaces the first match, not all of them. When I test it, string.match gives me an array with only 1 element for some unknown reason, but hopefully that's a start. It's weird though, cuz when I delete the chunk of the string that it found the first match in, string.match will return something later on in the string...but it should return everything at once...

Diki
Diki
  • Member since: Jan. 31, 2004
  • Offline.
Forum Stats
Moderator
Level 13
Programmer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 04:24 PM Reply

You could probably use the escape and unescape instead of regular expressions. If you're only using the data with AS3 that should work.

If you need to use regex you could try this.

egg82
egg82
  • Member since: Jun. 24, 2006
  • Offline.
Forum Stats
Member
Level 05
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 04:32 PM Reply

At 9/30/12 04:19 PM, MSGhero wrote: For the sake of argument, let's assume it's valid because the escape chars are actually there :)

haha, sure :)

I say "assume it's valid" because I want to be able to handle strings like this:
"this 'is' \'a\' test"
and this:
'this "is" \"a\" test'

at the same time. I'm not sure i'll be using the single-quoted string, but when it comes to security, better safe than sorry.

(/[^\\'][^\\"]/, "\'")

The double quote needs to go outside the [ ] or it gets treated like a single quote. And you need a | between the two cases. And replace only replaces the first match, not all of them.
public function san(input:String):String {
	var regex:RegExp = new RegExp(/[^\\+']|[^\\]+"/);
	while(input.match(regex){
		input = input.replace(regex, "\'");
	}
	regex = new RegExp(/\\\\/);
	while (input.match(regex)) {
		input = input.replace(regex, "\\");
	}
	return input;
}

better?

When I test it, string.match gives me an array with only 1 element for some unknown reason, but hopefully that's a start. It's weird though, cuz when I delete the chunk of the string that it found the first match in, string.match will return something later on in the string...but it should return everything at once...

what do you mean by that? O.o


Programming stuffs (tutorials and extras)
PM me (instead of MintPaw) if you're confuzzled.
thank Skaren for the sig :P

BBS Signature
egg82
egg82
  • Member since: Jun. 24, 2006
  • Offline.
Forum Stats
Member
Level 05
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 04:44 PM Reply

At 9/30/12 04:24 PM, Diki wrote: You could probably use the escape and unescape instead of regular expressions. If you're only using the data with AS3 that should work.

you deserve a hug, sir.

If you need to use regex you could try this.

two hugs :D

saved me from pulling my hair out, but i'm still kinda curious as to what the regex would look like.


Programming stuffs (tutorials and extras)
PM me (instead of MintPaw) if you're confuzzled.
thank Skaren for the sig :P

BBS Signature
MSGhero
MSGhero
  • Member since: Dec. 15, 2010
  • Offline.
Forum Stats
Supporter
Level 16
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 04:48 PM Reply

At 9/30/12 04:32 PM, egg82 wrote: what do you mean by that? O.o

I'm probably doing something wrong with the regexp, but it looks like this to me:

s = "111";
s.match("1"); // length should be 3, instead is 1

Also, I'm not reading your code because, yeah, I'd pull my hair out too.

egg82
egg82
  • Member since: Jun. 24, 2006
  • Offline.
Forum Stats
Member
Level 05
Game Developer
Response to MySQL sanitization with regex Sep. 30th, 2012 @ 05:34 PM Reply

At 9/30/12 04:48 PM, MSGhero wrote: s = "111";
s.match("1"); // length should be 3, instead is 1

match only matches the first regex it finds, or if doesn't find one it returns -1. That's why I used a while loop.

and yeah. Diki's pretty good about finding stuff that prevent hair pulling :P

Also, for anyone who happens to stumble across this:
Unless you are 100% aware of exactly what you're doing and all of the security risks you impose; for the love of god, don't ever connect directly to a database from a client.
Don't assume any client is secure, ever.


Programming stuffs (tutorials and extras)
PM me (instead of MintPaw) if you're confuzzled.
thank Skaren for the sig :P

BBS Signature