Stop Quotes can stop sql injection or not

Question

1.00/5 (3 votes)

See more:

Hi,

Can any one tell me if I stop quotes i.e ' in my application or website. then also my application can be sql injected or not

As I see you have to write quote and then semi colon to start your own line like

'; DELETE FROM Table;--

Plz give some relevant example in favor of your answer

Thanks In Advance

Posted 23-Mar-15 6:24am

binadi007

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Afzaal Ahmad Zeeshan · Accepted Answer · 2015-03-23T06:31:00

Solution 1

No, they still don't prevent Sql Injection, you should use SqlParameters to pass your values to the SQL Server. See MSDN documentation[^] for more on this.

The worst scenario in SQL Injection has been provided by you... To delete the table and then comment out the rest of the statement. Even if you allow quotes or not, it will exploit your database, you (however) are going to pass SQL command in a string format, and what do you think prevents user from adding a quote himself?

If I have to design a pattern, I would also not rely on SQL parameters. I would first try to validate the user's input, before even creating or constructing the SQL command. If the input is ok, then I would create the SQL command, also again... Passing the values using SQL parameters.

Posted 23-Mar-15 6:31am

Afzaal Ahmad Zeeshan

Updated 23-Mar-15 6:37am

v4

Comments

binadi007 23-Mar-15 12:34pm

Any example after stopping quote can able to sql inject?

Afzaal Ahmad Zeeshan 23-Mar-15 12:39pm

I have added more details... Even if you try to remove quotes, user would still be able to add a few of his own. User can input anything, right? If you want to remove, remove quotes from the user input too; using input.Replace("'", "");. Then you can say, quotes have been removed. :)

binadi007 23-Mar-15 12:44pm

What if I replace single quote to double quote i.e input.Replace("'","''")?

Afzaal Ahmad Zeeshan 23-Mar-15 12:47pm

Wrong... When you will pass double string, the actual command string would break; it would be as the end of string has reached, other characters would be expected as variables or tokens and then a problem would generate again. Please see my answer again and try to understand the third-paragraph. You should validate the user input before creating the query. If the input is valid, then create and execute query, otherwise user is potential user; hacker or cracker, so you can tell him to fix his input.

binadi007 23-Mar-15 12:53pm

Can you provide any substantial link to understand your third paragraph better

Afzaal Ahmad Zeeshan 23-Mar-15 13:05pm

Sorry for belated reply, there are quite multiple solutions to this one, simple ones might have regular expressions to test what sort of input the user is passing, or to test whether the input is a command or not.

Please see the following links,

http://bytes.com/topic/c-sharp/answers/228326-validating-string
http://stackoverflow.com/questions/7587110/basic-user-input-string-validation
https://msdn.microsoft.com/en-us/library/gg680269(v=pandp.11).aspx

You will find enough overview of the third-paragraph that I have talked about. :)

binadi007 23-Mar-15 13:15pm

Fine I'm reading the given links by you. Still I'm eager to know any sql query injection which can overtake the condition input.replace("'","''");
This question is just because I'm using the same condition in my program and is still working fine, Even I use "'" symbol inside my textbox.

F-ES Sitecore 23-Mar-15 13:09pm

It won't break because a double apostrophe is how you encode a single apostrophe. Replacing ' with '' is the accepted solution for SQL.

binadi007 23-Mar-15 13:18pm

Yes I'm agreeing you with this concept completely. And As i'm lazy so I just want to know it's perfectly fine to overtake sql injection or not. Because parametrize way is little bit lengthy.

F-ES Sitecore 23-Mar-15 13:07pm

What if it was a name search and I was searching for O'Neil? If you remove the apostrophe altogether the query can stop working as intended.

binadi007 23-Mar-15 13:09pm

not to remove just convert one quote to double quote and works fine

F-ES Sitecore 23-Mar-15 13:11pm

Yes, that reply was in response to AAZ's suggestion of removing the apostrophe altogether.

Afzaal Ahmad Zeeshan 23-Mar-15 13:11pm

For such scenarios, as you've mentioned... It is better to see the third-paragraph, to validate the user-input. If you're allowing such names with single quotes you should allow to search for them too. It would also be better to single quotes into HTML encoded data; in fact to convert entire input as HTML encoded data. That can prevent the problems occurring through single quotes.

No logic would be perfect, you should always consider having a Plan B.

F-ES Sitecore 23-Mar-15 13:14pm

Why would I encode things as HTML in a desktop application? :)

Encoding text before storing simply causes issues down the line with editing. It is generally best to store data as-is and encode when you come to display, but that's slightly off-topic.

Afzaal Ahmad Zeeshan 23-Mar-15 13:17pm

No no, sorry... That is my bad for encoding into HTML. I got confused with this question and ASP.NET web application I am working on.

Yes, that is off-topic for this question, and using Regular expression or using double quotes would solve it.

Sergey Alexandrovich Kryukov 23-Mar-15 14:39pm

5ed; I added some more link to this, in particular, the Microsoft link addressing specifically SQL injection with ADO.NET.
Please see Solution 5.
—SA

Afzaal Ahmad Zeeshan 23-Mar-15 16:22pm

Thank you, Sergey sir.

LGM-Developer 23-Mar-15 17:26pm

Anyone know what website or application this guy is working on? There is no excuse for being lazy ... By intercepting quotes you are removing the legitimate reasons for having a quote in a name or surname field... If you use SQL parameters at the start, you will also remove all the BS that you will later encounter with string manipulation and concatenation spaghetti that results from doing "dynamic" sql inserts etc.

Peter Leow 23-Mar-15 22:27pm

Good effort, 5ed.

Afzaal Ahmad Zeeshan 23-Mar-15 22:40pm

Thank you for your vote, Peter.

Peter Leow · Accepted Answer · 2015-03-23T06:33:00

Solution 3

Find out how
1. SQL Injection[^] work.
2. Hack Proof Your ASP.NET Applications From SQL Injection[^]

Posted 23-Mar-15 6:33am

Peter Leow

Updated 23-Mar-15 6:37am

v2

Comments

Sergey Alexandrovich Kryukov 23-Mar-15 14:39pm

5ed; I added some more link to this, in particular, the Microsoft link addressing specifically SQL injection with ADO.NET.
Please see Solution 5.
—SA

Peter Leow 23-Mar-15 22:26pm

Thank you, Sergey.

Richard Deeming · Accepted Answer · 2015-03-23T08:34:00

Just Google "SQL Injection without quotes" for examples of why this won't protect you from SQLi. For example:
No single quotes is allowed, Is this SQL Injection point still exploitable?[^]
SQL Smuggling, or The Attack That Wasn't There[^]

The "Unicode Smuggling" section from the second link is of particular interest - certain Unicode characters which your code sees as distinct from the single-quote may automatically be translated by the DBMS to a single quote, rendering your filter useless.

Rather than wasting your time trying to argue that a naive filter will be good enough, just use properly parameterized queries. ADO.NET makes it extremely easy to do!

Sergey Alexandrovich Kryukov · Accepted Answer · 2015-03-23T08:37:00

Solution 5

In addition to Solutions 1 and 3:

This is the example of how SQL injection works: http://xkcd.com/327[^]. :-)
This is what you need to use: http://msdn.microsoft.com/en-us/library/ff648339.aspx[^].

See also my past answers on the topic:
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].

—SA

Posted 23-Mar-15 8:37am

Sergey Alexandrovich Kryukov

Comments

Afzaal Ahmad Zeeshan 23-Mar-15 16:23pm

+5.

Sergey Alexandrovich Kryukov 23-Mar-15 16:46pm

Thank you, Afzaal.
—SA

Peter Leow 23-Mar-15 22:26pm

+5.

Sergey Alexandrovich Kryukov 23-Mar-15 22:32pm

Thank you, Peter.
—SA

Santosh K. Tripathi 24-Mar-15 0:25am

+5 from me :)

Sergey Alexandrovich Kryukov 24-Mar-15 0:52am

Thank you, Santosh.
—SA

F-ES Sitecore · Accepted Answer · 2015-03-23T06:32:00

Solution 2

If you double-up any apostrophes in user input then that will protect against this attack

string sql = "select * from table where name = '" + userInput.Replace("'", "''") + "'";

However it is far better to use parameterised queries if using ado.net, or an ORM that automatically protects you against these things like Entity Framework.

Posted 23-Mar-15 6:32am

F-ES Sitecore

Comments

binadi007 23-Mar-15 12:42pm

Yeah I'm thinking the same that replace single quote to double quote can also protect the sql injection, and it is simple way too

Richard Deeming 23-Mar-15 14:36pm

No, that won't protect against SQLi, except for the simplest attacks. A quick search for "SQL Injection without quotes" provides enough evidence that this type of filter doesn't work.

F-ES Sitecore 23-Mar-15 15:20pm

I had a google and couldn't find anything relevant, maybe you could post an example of an attack that will still work?

Richard Deeming 23-Mar-15 15:21pm

Have a look at the links in my answer (Solution #4), particularly the "Unicode Smuggling" section of the "SQL Smuggling" paper.

F-ES Sitecore 23-Mar-15 15:36pm

Thanks, I looked at the PDF, albeit briefly, and those techniques seem to cover where validation is used as the SQL injection protection. The document actually states that OWASP recommends you defend against injection by escaping quotes (ie replace ' with '' in our instance). Again, if you could give me an actual code example or string that I could test I would be interested to see this working.

Richard Deeming 23-Mar-15 15:47pm

The PDF has an example on page 7, where the character U+02BC is ignored by the Replace call, but is treated as a single quote by SQL.

OWASP does briefly mention[^] using escaping to prevent SQLi, but only as a last resort. They explicitly state, "this methodology is frail compared to using parameterized queries and we cannot guarantee it will prevent all SQL Injection in all situations."

Given how simple it is to use properly parameterized queries in ADO.NET, not to mention the various ORMs and utility libraries, I don't understand why so many people seem to think it's worth their time trying to argue the merits of using string concatenation. Even if none of the 11 million members of CodeProject gives you a working example of SQLi bypassing your filter, is it really worth the risk? It only needs one person to find a way to bypass your filter, and you'll have to go back and re-write all of your code.

F-ES Sitecore 23-Mar-15 16:32pm

Cheers, I'll have a play around with that.

Stop Quotes can stop sql injection or not

5 solutions

Solution 1

Solution 3

Solution 4

Solution 5

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us