-4

Protecting mySQL queries by algorithms needs knowledge about exploits. Thus I'm checking the query whether it contains *. How to avoid using * in mySQL queries by using alternative syntax? Are there ways to show all database content without using SELECT * FROM

many thanks

Efried
  • 1
  • 3
  • 1
    *How to avoid using \** - simply don't use it, it's neither compulsory nor best practice for selecting columns, but perfectly acceptable for aggregations and correlations; by "all database content" you actually mean "all columns from a table". – Stu Jul 13 '22 at 10:40
  • *Thus I'm checking the query whether it contains \*.* The query may contain an asterisk in string literal, including JSON paths, as multiplying operator, and in `COUNT(*)`. – Akina Jul 13 '22 at 12:00

1 Answers1

0

The * wildcard means all columns of the table(s) referenced in the query.

The alternative that doesn't use * is to spell out the names of the columns:

SELECT column1, column2, column3, ... FROM ...

In my opinion, an algorithm that counts any SQL query that contains * as an exploit is designed wrong. There are many legitimate uses of that symbol in SQL queries. You might as well count any query that contains an OR boolean operator as an exploit (I have read about so-called security tools that do this, and it's just as misguided).

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks thats easy to parse. I'm searching for protecting fields potentially used a mysql input line – Efried Oct 01 '22 at 13:38
  • Do you plan to disallow `SELECT COUNT(*)...` queries too? – Bill Karwin Oct 01 '22 at 14:38
  • good question, I could exempt that. On the other hand, that would reveal field names which should not be queried for. Even if they are blocked, the information about them can benefit the possibility to hack. – Efried Oct 10 '22 at 08:13
  • How about queries with `/* ... */` comments? Or queries that use `*` for arithmetic multiplication? Perhaps you can see where I'm going with this. Using pattern-based methods for SQL injection defense is a losing battle. – Bill Karwin Oct 10 '22 at 15:18
  • Absolutely correct. But if you see data extraction as first step, the need owner can multiply the extracted fields later. What would be the alternate way to pattern based extraction without sacrificing freedom for generating queries? – Efried Oct 12 '22 at 07:04
  • None. The solution used by SQL "firewall" vendors like Oracle or GreenSQL is to create an "allowlist" of known legitimate SQL queries. You run your application in learning mode to teach the firewall which queries are known, then in production it will block anything that is unknown. If you change queries in your application code, you need to repeat the learning stage. This cannot work with an application that runs totally unpredictable queries. – Bill Karwin Oct 12 '22 at 15:32
  • Some vendors used to use pattern-based approaches like you are trying to implement, but many of them have removed those features from their product. The reason is that it's not practical to use pattern-based approaches, because it has a high risk of restricting legitimate queries as much as illicit queries. – Bill Karwin Oct 12 '22 at 15:38
  • I don't understand 'none' as answer. If you have data and do want making it accessible you may: a allow for pre-defined queries you have to implement, or b check query if allowed manually, or c have a SQL parser. a reduces the ability to experiment, b creates much more effort IMHO. The EU votes for data havens for data science, and this will fail because of privacy concerns. The parser approach is a good compromise. – Efried Oct 13 '22 at 08:47
  • Yes, if you can parse the SQL syntax, that's an alternative. I understood your question to be asking for alternative ways to implement pattern-based query validation. I think that's not a practical strategy. The other alternative is to allow users to choose one of a set of predefined queries that you have vetted. But not to allow users to run an arbitrary query. – Bill Karwin Oct 13 '22 at 23:05