0

I want to create SQL queries in a language like Java or C#. Everybody knowns that you must not do this:

sql = "SELECT * FROM T WHERE A='" + someString + "'"

Because this allows code injection, for example with:

someString = "xxx';DROP TABLE T;DECLARE @A CHAR(3) ='xxx"

Everybody knowns you must use proper SQL parameters provided by the standard apis available in most languages.

But for some reason that is too complex to explain (please assume my reasons are good), I can't or don't want to use proper parameters and need to stick to the dangerous method of formatting the string myself.

My question is simple. Is the following safe:

 sql = "SELECT * FROM T WHERE A='" + someString.Replace("'","''") + "'"

If you think this depends to on the RDBMS, please stick to MySQL.

Benoit Sanchez
  • 681
  • 6
  • 21
  • Yes. It is called *parameters*. Use parameters to pass in values. Don't munge query strings, if you can avoid it. – Gordon Linoff Oct 17 '18 at 11:05
  • 1
    Follow Gordon's above advice. If you _must_ concatenate something, then check if your API has a function to sterilize the inputs before concatenation, which might have the same effect as using a prepared statement. – Tim Biegeleisen Oct 17 '18 at 11:05
  • Back in the old days when I was working on websites in asp3 and ms-access databases, The company I worked for had a function to replace dangerous chars in strings. I can't remember what chars it replaces, but `'` was defiantly one of them. That still didn't stop us from from getting some of our websites to get SQL Injection attacks, so the answer is no - it's not even close to being totally safe. – Zohar Peled Oct 17 '18 at 11:13
  • 1
    I'm now curious about the reasons you can't/don't want to use parameters – Cid Oct 17 '18 at 11:16
  • What programming language are you using for `sql = whatever`? – O. Jones Oct 17 '18 at 11:25
  • @O.Jones take a look at the first line – Cid Oct 17 '18 at 11:30
  • 1
    It is not safe. Injection is not only done with quotes. I have done it without using a single quote in MS SQL, I would assume MySQL is not very different. Not only unsafe, it would lead to erroneous results even if were used only by the good guys (ie: O'Hara would end up being O''hara in the database assuming no injection were ever intended). – Cetin Basoz Oct 17 '18 at 11:44

2 Answers2

1

Not even by a long shot.

I'm not even going to try to create some viable modifications or recommendations because it is a lost case. Any of them would just make you fall into the false sense of security.

Let me just give you a small list of links to introduce yourself:

SQL Injection Cheat Sheet

... check 'If statements' or 'Strings without Quotes' parts

Bobby tables

And various SO questions:

What's the best method for sanitizing user input with PHP?

Which characters are actually capable of causing SQL injection in mysql

Does eliminating dangerous characters avoid SQL-injection?

How can I prevent SQL injection in PHP?

Does this code prevent SQL injection?

What characters have to be escaped to prevent (My)SQL injections?

Do htmlspecialchars and mysql_real_escape_string keep my PHP code safe from injection?

Loki
  • 134
  • 1
  • 2
  • 8
0

First of all, it's bad practice.

It will be bad in two condition I considered:

  1. if the field is not VARCAHR, it is INT? It won't work.
  2. if input is "it's ok", it will be "it"s ok", it will be tedious for converting if it is username should be presented.

It will be more problems with your method(But I haven't considered). The best way is using parameters.

pwxcoo
  • 2,903
  • 2
  • 15
  • 21