Solved

Can I stop SQL injection by escaping characters?

Posted on 2014-07-25
10
437 Views
Last Modified: 2014-07-29
I know that the best way to avoid SQL injection attacks is to use parameterized queries or preparedStatements in Java because there would be no string parsing that be misdirected.

However, I have an application that is 12 years old and it is just not worth the effort to re-architect the app so I can eliminate all the string concatenation.

I have seen that MySQL has a function mysql_real_escape_string()  that escapes 7 different characters:

mysql_real_escape_string() calls MySQL's library function 3. mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

So, I am wondering if that should be enough to prevent SQL injection attacks?
0
Comment
Question by:jkurant
10 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 40220489
So, I am wondering if that should be enough to prevent SQL injection attacks?
How would you use that without rewriting the code?

In any case, it might help, but would not be sufficient
0
 

Author Comment

by:jkurant
ID: 40220524
i would pass any strings I was going to execute on the database server through a routine like mysql_real_escape_string() to escape the characters that could be used in an attack. That wouldn't require re-writing code, just refactoring it a bit.

If string replacement is not sufficient, I may have to re-write the app, meaning re-design it so as to not build SQL strings containing user input at all, but rather passing those strings in as arguments.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 40220537
That wouldn't require re-writing code, just refactoring it a bit.
Not sure how you can avoid rewriting or how indeed refactoring is not rewriting.

The point is, (say) nx2 effective rewriting is better than n ineffective rewriting
0
 

Author Comment

by:jkurant
ID: 40220621
@CEHJ: Neither of your comments contain anything like an answer to my question. If you have something helpful to add, please do.

Can anyone think of a SQL injection attack that could succeed even with escaping the following characters: \x00, \n, \r, \, ', " and \x1a
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 40220624
I have answered your question. I'm sorry you don't like the answer
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jkurant
ID: 40220664
This wasn't really a yes or no question. If escaping those 7 characters will not prevent SQL injection, why won't it? What kind of attack could defeat that defense?
0
 
LVL 26

Accepted Solution

by:
dpearson earned 300 total points
ID: 40221006
If you're not willing to switch to Prepared Statements then I think your escaping function is the next best option.

I've heard that the weakness for these methods is folks coming up with clever Unicode encodings of strings - which is a hugely complex area in MySQL (the client, the server, the database and the table can all be encoding and decoding the strings in and out of UTF-8/ASCII etc as they travel) and that with the right Unicode encoding, you can bipass the ASCII-centric string escaping approaches.

So escaping your character string puts another barrier in front of an attacker, but I think proving that it's 100% injection resistant would be exceptionally hard.  It really depends how bad it would be if the data was extracted/corrupted by an attacker.  If this is for a database of where you store your MP3s I'd say you're good to go.  If  it's a corporate database of passwords, I think maybe not so good.

Doug
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 40221050
If escaping those 7 characters will not prevent SQL injection, why won't it?
This is not really the place to go into sql injection in detail, but the facts are that

a. it's not limited to just the use of escape characters
b. the set of characters you mentioned is limited

http://en.wikipedia.org/wiki/SQL_injection
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 40221900
The Technet article SQL Injection goes to some length to explain some of the subtleties involved with SQL Injection, as well as lists the characters you should escape (they are not the same as MySQL)

And yes, I do understand that all you can do is reduce the risk, as changing your code to use Stored Procedures or parameterized queries is out of the question.
0
 

Author Closing Comment

by:jkurant
ID: 40226952
I eventually found this, which is very helpful. https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now