1

I know that sanitizing parameters (removing quotes for example) is not a perfect solution against SQL injection when you can't use setParameters().

But is it safe to sanitize parameters by checking if they (parameters) don't contain strings with an empty space after it as you see below???

'DELETE ', 'ALTER ', 'DROP ', 'SELECT ', 'TABLE '

For example, email parameter was passed to server but it contains DROP keyword: String param = "john@mail'DROP myTable"

SELECT * from Users where email = 'john@mail'DROP mytable'

So, my table is dropped;

Notice the space I left after each keyword. This way if a data in db contain the listed keywords but without space, then it will be allowed to use, otherwise it should be a hacker trying to harm my data (ALTER TABLE mydb).

Please feel free to add as example any SQL engine: SQL, Oracle, HANA, MySQL

Thanks

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Octtavius
  • 563
  • 8
  • 29
  • 5
    The best practices for preventing SQL injection are highly dependant on the DBMS you are using. Crackers are smart and while some measures can help with a product, it might not with another one. – EzLo Jun 13 '18 at 14:58
  • 2
    No, for instance in your case if the email provided by the user is `'john@mail' or 1=1`, a hacker may get the list of all the users. All the DBMSs you suggest support parameters so why not use them instead of dangerous (and potentially poorly performing) string concatenations? – vc 74 Jun 13 '18 at 15:11
  • @vc74 I know but sometime you can't go away without concatenation. Like in the question I posted here https://stackoverflow.com/questions/50838380/how-to-pass-objects-or-grouped-params-to-sql-in-clause – Octtavius Jun 13 '18 at 15:13
  • @EzLo - I'm not sure that is true. What SQL based DBMS' are there out there where the best solution _isn't_ parameterization? – Abe Miessler Jun 13 '18 at 15:15
  • @Octtavius Some DBMSs allow collection parameters in SQL queries and when not available, you can probably create a stored procedure taking a collection parameter. – vc 74 Jun 13 '18 at 15:22
  • 1
    @Octtavius, I answered your previous question. You *can* use parameters. – Bill Karwin Jun 13 '18 at 15:25

1 Answers1

4

No this is not sufficient.

Please follow the industry standards for preventing SQL Injection that are laid out by OWASP.

One obvious example of SQL injection for the query you posted would be if someone provided the following input for user:

john@mail' OR '1' = '1

Which would produce the query:

SELECT * from Users where email = 'john@mail' OR '1' = '1'

They could also inject a UNION to start selecting data from other tables. There are probably even more devastating examples.

The bottom line is never try to "roll your own" SQL Injection protection. Much smarter people than you and I have tried to solve this problem and the currently accepted standard of using parameterized queries is simply the best solution we currently have available.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • Thanks but I wanted to know in case when you cannot use setparameters. Like in a question I posted here https://stackoverflow.com/questions/50838380/how-to-pass-objects-or-grouped-params-to-sql-in-clause I couldn't find a way to use parameters so I might need to do some sanitation, manually. – Octtavius Jun 13 '18 at 15:19
  • 2
    I understand that and I answered your question - no, it's not safe. You can't do it. I also posted examples of why this doesn't work. You should be asking how you can use parameterized queries if you _think_ you can't. I say this because I've never seen a situation when parameterized queries werent an option, so it's likely that you actually can use them. – Abe Miessler Jun 13 '18 at 15:21
  • Thanks Abe, that's a good advise to think about "how can I use parameterized queries". – Octtavius Jun 13 '18 at 15:27
  • I think someone just posted another answer to your previous question. Maybe that will work? I'm not familiar with that particular tech so I can't really say. Good luck! – Abe Miessler Jun 13 '18 at 15:28