1

I am developing an application, but I am not sure, how to protect the database from SQL injection.

I would like to ask, for instance in case of using base64 for any data that inputs in the tabes (strings only).

For instance on PHP code it will look like this

$data = $_POST['input'];

$base64 = base64_encode($data);

mysqli_query($conn,"INSERT INTO table(`string`) VALUES ('$base64')");
Dharman
  • 30,962
  • 25
  • 85
  • 135
Mark Shaposhnik
  • 11
  • 2
  • 6
    Why not just parametrise, and avoid the problem completely..? – Thom A Sep 14 '21 at 09:57
  • 1
    Also, you've tagged SQL Server here, however, you're using the `mysqli` library in your PHP (which unsurprisingly is for MySQL), *and* your delimiter identifier in your SQL is a backtick (`\``), which is also for MySQL and isn't a delimit identifier in T-SQL. – Thom A Sep 14 '21 at 09:58
  • 1
    Why would you want to insert gibberish into your database? Where are the single quotes in your example? – Salman A Sep 14 '21 at 10:04
  • https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php This question will resolve any clarifications or questions that you have – Hirumina Sep 14 '21 at 10:05
  • 2
    Use parameterised SQL statements, and forget about SQL Injection. Don't reinvent the wheel. – The Impaler Sep 14 '21 at 11:45

1 Answers1

2

No, it's not possible to prevent SQL injection as you have just demonstrated in your code (now edited to include proper string formatting). It is going to be very difficult to abuse this vulnerability though. However, I see no reason why you are even considering this. Why not use prepared statements?

The only safe way is to never allow any variable input that is not whitelisted in the query. This means that all data parts should be parameterized and bound using prepared statements. Dynamic SQL query parts need to be constants, hardcoded string literals, or properly whitelisted.

You can't prevent SQL injection 100% as this is a programmer error. There will always be a junior developer that will concatenate a variable directly into SQL. Base64 is not a preventive measure against SQL injection. It's a nasty workaround, but if you have developers that are allowed to make mistakes and put variables in SQL, then you will have developers that also forget to Base64 encode strings. Adding this extra step would just make it easier to introduce SQL injection.

Dharman
  • 30,962
  • 25
  • 85
  • 135
  • While I agree with you ofc, I don't think the above is actually exploitable in its current form as the base64 output will only ever contain letters, numbers, `+`, `/`. and `=` (only at the end for padding). It's still a bad idea for a whole lot of reasons, but strictly speaking as it's written above, it's not vulnerable. :) – Gabor Lengyel Sep 14 '21 at 11:59
  • @GaborLengyel No, not at its current form – Dharman Sep 14 '21 at 15:52