Protecting against SQL injection

[mwadden]

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

[darnpunk]

I think you might be looking for this? - http://www.smartfoxserver.com/docs/docP ... Quotes.htm

[mwadden]

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

[darnpunk]

Welcome!

[potmo]

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.

[Lapo]

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/

[duke]

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

[BigFIsh]

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