Protecting against SQL injection

Post here your questions about Actionscript and Java server side extensions development.

Moderators: Lapo, Bax

mwadden
Posts: 6
Joined: 27 Aug 2007, 20:14

Protecting against SQL injection

Postby mwadden » 27 Aug 2007, 20:22

Hi All,

Does anyone have any suggestions on how to sanitize user submitted variables on the server side, so that I'm protected from SQL injection?

For example, I want to do this but it's clearly not safe:

Code: Select all

var sql = "select * from users where userName='" + nick + "' and password='" + pass + "'";


Normally I'd use an escaping function (in .net, coldfusion, php, etc) or some regular expressions, but I don't think either of those are available in SFS ActionScript (or are they?).

Thoughts?


-mark
User avatar
darnpunk
Posts: 229
Joined: 22 Jun 2007, 02:58
Location: SG

Postby darnpunk » 29 Aug 2007, 00:33

I think you might be looking for this? - http://www.smartfoxserver.com/docs/docP ... Quotes.htm
mwadden
Posts: 6
Joined: 27 Aug 2007, 20:14

Postby mwadden » 29 Aug 2007, 02:30

Ah, I didn't see that. That'll do the trick provided the sql fields are quoted to begin with, but I don't think it'll help if it's a numeric field that's not quoted in the sql statement.

Either way, it'll work for me for now, so it's definitely a good place to start.

Thanks for the help!


-mark
User avatar
darnpunk
Posts: 229
Joined: 22 Jun 2007, 02:58
Location: SG

Postby darnpunk » 29 Aug 2007, 02:34

Welcome! :lol:
User avatar
potmo
Posts: 154
Joined: 02 Feb 2007, 14:28
Location: Stockholm Sweden
Contact:

Postby potmo » 29 Aug 2007, 13:36

there are a lot to think about when protecting yourself from SQL-inections.
i think the best way is to only accept A-z 0-9 characters to be passed in to a sql query.

One thing that i just thinked about is to base64-encode all the messages and store them in the database. It will take up more space but its harder to inject data into the SQL. Sometimes you want to have all chars avalible for a user (i.e when a user sends a chat-message to an other user and you want to log it)

Anyone who knows this better? I guess there's loads of information on this and loads of workarounds and hacks.
Nisse Bergman
Raketspel
User avatar
Lapo
Site Admin
Posts: 21286
Joined: 21 Mar 2005, 09:50
Location: Italy

Postby Lapo » 30 Aug 2007, 03:18

One nice way to avoid SQL problems and also get rid of SQL at all is to use an ORM (Object Relational Mapping) like Hibernate, Ibatis etc...

These tools provide a higher level of abstraction, practically hiding the database interaction from your code and allowing you to use plain objects instead of horrible and error-prone SQL commands.

We used them in a couple of projects based on SFS, paired with Python on the server side they helped a lot with productivity and code readability.

Learn more here:
http://www.hibernate.org/
http://ibatis.apache.org/
https://activeobjects.dev.java.net/
Lapo
--
gotoAndPlay()
...addicted to flash games
duke
Posts: 31
Joined: 16 Apr 2009, 11:23

Postby duke » 09 Aug 2009, 02:00

Sorry to raise this thread from the dead, but i'm curious to know - what's the advantage of using the escapeQuotes method over using Java's prepared statements?

http://java.sun.com/docs/books/tutorial ... pared.html
User avatar
BigFIsh
Posts: 1698
Joined: 25 Feb 2008, 19:26
Location: New Zealand

Postby BigFIsh » 09 Aug 2009, 23:01

Perpared statement simply means something like this:

"SELECT * FROM Something WHERE code = '?'"

where ? is the placeholder for your variable. So instead of having to send the whole mysql query from the client, you have a "prepared" mysql statement on server side which makes it much safer."

escapeQuotes simply make all harmful mysql query modifications such as \ " and ' harmless.

for example, a client could inject your database by perparing a placeholder variable of "' OR 1'" (OR clause of 1 will always be true thus every single entry in your table will be selected). escapeQuotes prevents this by making single quotes harmless by adding \ to it, thus it is considered as a string. Thus, mysql would just throw an error or "row not found".
Smartfox's forum is my daily newspaper.

Return to “Server Side Extension Development”

Who is online

Users browsing this forum: No registered users and 3 guests