0

Is there a way to restrict php pdo execute() to run just the first sql statement?

For instance running

SELECT * FROM customer;DROP TABLE invoice

will return all customers but it will also delete the invoice table.

I have a situation where I need a whole SQL statement from a user but it must be a SELECT and nothing additional.

My example is just one of many that could be maliciously entered. It might include additional DROP, UPDATE, DELETE statements etc.

A prepared statement will simply replace ?s with values. It will not stop dangerous SQL statemets being passed to it.

This would not be a problem if there was a way to restrict php pdo execute() to run just the first sql statement?

user1397366
  • 1
  • 4
  • Why would you put that statement in there if you don't require it? And if you're worried about injection then Prepared statements in PDO will themselves take care of that – Hanky Panky May 22 '14 at 04:13
  • if you specifically want to delete if your query contains drop query, and if you have that query in some variable then `preg_replace("/(.*?)DROP(.*)/", "$1", "SELECT * FROM customer;DROP TABLE invoice ");` will delete drop query if it's present in that query – ɹɐqʞɐ zoɹǝɟ May 22 '14 at 04:17
  • @user790454 - That can fail if selecting a substring or inserting a substring that contains the word drop. – Scopey May 22 '14 at 04:30
  • yes,then we can replace drop with ; – ɹɐqʞɐ zoɹǝɟ May 22 '14 at 06:19
  • My example is just one of many that could be maliciously entered. It might be DROP, UPDATE, DELETE.@Hanky 웃 Panky how do I make this a prepared statement as I am not actually replacing ?s with values? – user1397366 May 22 '14 at 18:16
  • I like the approach suggested in [this answer](http://stackoverflow.com/a/13064261/622391) to the marked duplicate - run the query with a user account which only has `SELECT` privileges. – Simon MᶜKenzie May 23 '14 at 00:13
  • @Simon MᶜKenzie - I have places in the same application that needs to run DELETES and APPENDS and INSERTS, places the user cannot access. Could the answer to my original question simply be No? – user1397366 May 23 '14 at 05:29
  • Why are people insisting that this question has already been asked? – user1397366 May 23 '14 at 05:35
  • @user1397366, perhaps running code supplied by the user vs. your own "clean" code justifies running the user code in a more locked-down environment (i.e. via a different account). After all, that's why roles exist. As to your actual question, doesn't `explode(';',sql)[0]` get rid of all subsequent statements (assuming no semicolons in strings)? – Simon MᶜKenzie May 23 '14 at 05:46
  • 1
    I'll tell you why. Imagine you are gong to build a car. And then comes along asking "how to make square wheels for a car"? Everyone would point you out to the question where construction of round wheels is explained. The problem with programming is that everyone taking it as a picnic. Everything can be done and anyone can make everything. No rules, no knowledge, no restrictions. Just a free flow of imagination required. In reality a car won't go on square wheels. In reality you should not receive no raw SQL from a user, unless you are definitely and positively know what are you doing. – Your Common Sense May 23 '14 at 06:01
  • @Your Common Sense Poor analogy (still not answering the question). – user1397366 May 23 '14 at 22:26

1 Answers1

0

IF your trying to prevent SQL injection, prepare statements can handle it.

you can use something like this to prevent SQL injection 

$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Saqueib
  • 3,484
  • 3
  • 33
  • 56
  • Thanks for your response but I am not actually replacing ?s with values. It is a case where I need to accept an sql string from the user and not just values, it must be a SELECT statement and may do damage to the system otherwise. – user1397366 May 22 '14 at 18:30
  • 1
    in that case you would need Keyword blacklist, or simply change the DB prevellage so that its not possible to drop table; – Saqueib May 23 '14 at 08:22