0

I'm making school web application which will be for testing users SQL knowledge and they have to enter SQL queries in form textarea and execute it on server mySQL. the problem that occurred to me is that I can't do prepared statements or binding or white-list on unknown dynamic queries(entered by user) and this makes the application SQL injection vulnerable.

How can I prevent SQL Injection on this type of queries(allowed will be SELECT, DELETE, INSERT, UPDATE) without affecting the final compare result?

Maybe I'm missing some PHP function which can do that? or I have to create a SQL parser which will check user queries for possible SQL injections? I'm not trying to do anything fancy, I just need basic protection for my application.

I use CodeIgniter as Framework, but I don't think this will change anything.

How can I use PDO, statements,whitelists and other techniques, when user can enter queries like

 select * from user where some_column = some_value (this is just example)

or query like

select * from user LEFT JOIN ..... having something > 5 ORDER by some_column

I can't predict their input and all possible combinations can contain injection vulnerability.

I receive on my end $_POST['SQL_user_query'] which contains string of SQL query, I can't use on it prepared statement.

EDIT: what i want is that is to prevent the users to do destructive things with my database and gain information about the tables, because after they submit the query the web application also will show the result from the query under the input textarea and if they make injection that can show sensitive information.

mwebber
  • 376
  • 5
  • 14
  • Your question is slightly unclear. Are you planning to execute SQL given by users? Shouldn't you just parse it somehow, if you're just doing tests. – James Z Aug 15 '15 at 17:30
  • @JamesZ yes i execute SQL given users and must compare it returned results – mwebber Aug 15 '15 at 17:31
  • I think "SQL injection" is the wrong phrase here - you _want_ SQL injection, really - you are inviting the user to inject arbitrary SQL. Ask yourself: what statements would you wish to disallow? It seems that you are specifically allowing `DELETE` and `UPDATE`, which can be destructive. – halfer Aug 15 '15 at 17:34
  • 1
    If you just want to insulate a user's actions from the rest of the database, then just run everything in a transaction, and then roll it back once you have examined the result. This is how SQL Fiddle works, I believe. – halfer Aug 15 '15 at 17:36
  • 1
    I would assume this could be done using user rights, so that the user can't do anything they are not supposed to be able to do. – James Z Aug 15 '15 at 17:36
  • @halfer are they safe this "transactions" ? where can i read more about them? – mwebber Aug 15 '15 at 17:38
  • 1
    What do you mean by safe? [Read more here](https://dev.mysql.com/doc/refman/5.5/en/commit.html). – halfer Aug 15 '15 at 17:38
  • @halfer sql injection proof, its first time i hear about them, will check them, thanks! – mwebber Aug 15 '15 at 17:39
  • 1
    They have no effect on whether you have SQL injection vulnerabilities. This question isn't about SQL injections. Again, you need to explain what statements you would want to reject. Can you offer an example? – halfer Aug 15 '15 at 17:39
  • It would probably be better to create a new question into dba.stackexchange.com and rephrase the question better (and leave out SQL injection) -- and tag correctly, either SQL Server or MySQL, assuming this isn't going to support both – James Z Aug 15 '15 at 17:41
  • @M.Ali - would you cast a reopen vote please? – halfer Aug 15 '15 at 17:48
  • @halfer i want to reject them from receiving information about the mysql server its tables,columns etc and to prevent to enter hex/base64 encoded strings to execute. as i stated above they will have to see result from their query... if its normal select its ok, but if its select with injected table schema... i will check the transactions and if they do what i want i will try them. thanks! – mwebber Aug 15 '15 at 18:02
  • 1
    Your first step is to limit the connection to just your four statements, as @JamesZ says. Use a spare database for this, and only grant user permissions on that database - they should not be able to reach the MySQL system tables (of course do check this manually). – halfer Aug 15 '15 at 18:13
  • 1
    (I voted to reopen this, but I don't think anyone else has, or is going to. Do you have enough information in the comments to be getting on with?) – halfer Aug 20 '15 at 08:53
  • 1
    @halfer yes, thank you for your help! – mwebber Aug 20 '15 at 15:53

0 Answers0