Is there some way to inject SQL even if the ' character is deleted?

Question

If from an SQL query I remove all the ' characters, is there some other way to do an SQL injection attack on the database?

How can it be done? Can anyone give me examples?

Answer 1

Yes, depending on the statement you are using. You are better off protecting yourself either by using Stored Procedures, or at least parameterised queries.

See WikiPedia for prevention samples.

Answer 2

encoding data in hex?

this might help http://www.0x000000.com/index.php?i=14

Answer 3

. . . uh about 50000000 other ways

maybe somthing like 5; drop table employees; --

resulting sql may be something like: select * from somewhere where number = 5; drop table employees; -- and sadfsf

(-- starts a comment)

Answer 4

It depends on how you put together the query, but in essence yes.

For example, in Java if you were to do this (deliberately egregious example):

 String query = "SELECT name_ from Customer WHERE ID = " + request.getParameter("id");

then there's a good chance you are opening yourself up to an injection attack.

Java has some useful tools to protect against these, such as PreparedStatements (where you pass in a string like "SELECT name_ from Customer WHERE ID = ?" and the JDBC layer handles escapes while replacing the ? tokens for you), but some other languages are not so helpful for this.

Answer 5

Yes, absolutely: depending on your SQL dialect and such, there are many ways to achieve injection that do not use the apostrophe.

The only reliable defense against SQL injection attacks is using the parameterized SQL statement support offered by your database interface.

Answer 6

Yes, there is. An excerpt from Wikipedia

"SELECT * FROM data WHERE id = " + a_variable + ";"

It is clear from this statement that the author intended a_variable to be a number correlating to the "id" field. However, if it is in fact a string then the end user may manipulate the statement as they choose, thereby bypassing the need for escape characters. For example, setting a_variable to

1;DROP TABLE users

will drop (delete) the "users" table from the database, since the SQL would be rendered as follows:

SELECT * FROM DATA WHERE id=1;DROP TABLE users;

SQL injection is not a simple attack to fight. I would do very careful research if I were you.

Answer 7

Thing is apostrophe's maybe genuine input and you have to escape them by doubling them up when you are using inline SQL in your code. What you are looking for is a regex pattern like:

\;.*--\

A semi colon used to prematurely end the genuine statement, some injected SQL followed by a double hyphen to comment out the trailing SQL from the original genuine statement. The hyphens may be omitted in the attack.

Therefore the answer is: No, simply removing apostrophes does not gaurantee you safety from SQL Injection.

Answer 8

Rather that trying to figure out which characters to filter out, I'd stick to parametrized queries instead, and remove the problem entirely.

Answer 9

Yes, it is definitively possible.

If you have a form where you expect an integer to make your next SELECT statement, then you can enter anything similar:

SELECT * FROM thingy WHERE attributeID=

• 5 (good answer, no problem)
• 5; DROP table users; (bad, bad, bad...)

The following website details further classical SQL injection technics: SQL Injection cheat sheet.

Using parametrized queries or stored procedures is not any better. These are just pre-made queries using the passed parameters, which can be source of injection just as well. It is also described on this page: Attacking Stored Procedures in SQL.

Now, if you supress the simple quote, you prevent only a given set of attack. But not all of them.

As always, do not trust data coming from the outside. Filter them at these 3 levels:

• Interface level for obvious stuff (a drop down select list is better than a free text field)
• Logical level for checks related to data nature (int, string, length), permissions (can this type of data be used by this user at this page)...
• Database access level (escape simple quote...).

Have fun and don't forget to check WikiPedia for answers.

/Vey

Answer 10

Also- even if you do just look for the apostrophe, you don't want to remove it. You want to escape it. You do that by replacing every apostrophe with two apostrophes.

But parameterized queries/stored procedures are so much better.

Answer 11

Parameterized inline SQL or parameterized stored procedures is the best way to protect yourself. As others have pointed out, simply stripping/escaping the single quote character is not enough.

You will notice that I specifically talk about "parameterized" stored procedures. Simply using a stored procedure is not enough either if you revert to concatenating the procedure's passed parameters together. In other words, wrapping the exact same vulnerable SQL statement in a stored procedure does not make it any safer. You need to use parameters in your stored procedure just like you would with inline SQL.

Answer 12

I can only repeat what others have said. Parametrized SQL is the way to go. Sure, it is a bit of a pain in the butt coding it - but once you have done it once, then it isn't difficult to cut and paste that code, and making the modifications you need. We have a lot of .Net applications that allow web site visitors specify a whole range of search criteria, and the code builds the SQL Select statement on the fly - but everything that could have been entered by a user goes into a parameter.

Answer 13

I suggest you pass the variables as parameters, and not build your own SQL. Otherwise there will allways be a way to do a SQL injection, in manners that we currently are unaware off.

The code you create is then something like:

' Not Tested
var sql = "SELECT * FROM data WHERE id = @id";
var cmd = new SqlCommand(sql, myConnection);
cmd.Parameters.AddWithValue("@id", request.getParameter("id"));

If you have a name like mine with an ' in it. It is very annoying that all '-characters are removed or marked as invalid.

You also might want to look at this Stackoverflow question about SQL Injections.

Answer 14

When you are expecting a numeric parameter, you should always be validating the input to make sure it's numeric. Beyond helping to protect against injection, the validation step will make the app more user friendly.

If you ever receive id = "hello" when you expected id = 1044, it's always better to return a useful error to the user instead of letting the database return an error.

Answer 15

Since this a relatively older question, I wont bother writing up a complete and comprehensive answer, since most aspects of that answer have been mentioned here by one poster or another.
I do find it necessary, however, to bring up another issue that was not touched on by anyone here - SQL Smuggling. In certain situations, it is possible to "smuggle" the quote character ' into your query even if you tried to remove it. In fact, this may be possible even if you used proper commands, parameters, Stored Procedures, etc.

Check out the full research paper at http://www.comsecglobal.com/FrameWork/Upload/SQL_Smuggling.pdf (disclosure, I was the primary researcher on this) or just google "SQL Smuggling".