0

I am forced to generate dynamic SQL. I realize it greatly complicates matters to do so, and the below example is silly and obviously does not require dynamic SQL, and is just used to illustrate this question.

Escaping user provided data is not enough, and the 3rd query in the below script is suspect to SQL Injection.

I have found that it is generally easiest to design the application so that all user inputs are integers, and simply typecast their input using (int)$_POST['user_input'].

I now have a need where the user_input needs to be text. What should I do to prevent SQL injection? One option is PHP's ctype_alpha(), however, I don't want "user,input" to result as FALSE, but should either remain as is or be converted to "userinput" (either scenerio is okay for me). I am thinking of something like $user_input= preg_replace('/[^a-z0-9,]/i', '', $_POST['user_input']);. Will this be 100% safe?

EDIT

Note that I am not executing the below query, but inserting it into a database, and as such, prepared statements are not appliable. You might believe that dynamic SQL generation should never be used, but telling me to use prepared statements is not right.

<?php
$id=123;
$query='SELECT * FROM someTable WHERE someColumn="'.$_POST['user_input'].'"';
$sql='INSERT INTO meta_table (id,sql) VALUES (?,?)';
$stmt=db::db()->prepare($sql);
$stmt->execute(array($id,$query));

$sql='SELECT sql FROM meta_table WHERE id=123';
$stmt = db::db()->exec($sql);
$query=$stmt->fetchColumn();

$stmt = db::db()->exec($query); //Prone to SQL Injection
$rs=$stmt->fetchColumn();
?>
Gumbo
  • 643,351
  • 109
  • 780
  • 844
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • 1
    Although for the *really* dynamical SQL it's quite a complex task, I see nothing too dynamical in your queries. – Your Common Sense May 09 '14 at 11:30
  • @YourCommonSense. Yes, I know this example is academic, and was just used as a silly example. – user1032531 May 09 '14 at 11:47
  • It's all right, you've got your academic answer as well – Your Common Sense May 09 '14 at 11:48
  • "you've got your academic answer as well" Sweet! Where? – user1032531 May 09 '14 at 11:50
  • [here](http://stackoverflow.com/questions/23563440/preventing-sql-injection-with-dynamical-sql?noredirect=1#comment36156858_23563440) – Your Common Sense May 09 '14 at 11:51
  • Wow, a copied, double copied plagiarized post! Note that this will not protect against injection when user input is used to generate SQL as I showed in my example. – user1032531 May 09 '14 at 11:54
  • it will. if you have a whim not to follow the proper practice for **all** your queries, it's **entirely and solely your own fault**, not the practice's. – Your Common Sense May 09 '14 at 11:58
  • Please don't discount this question. The referenced answer does not at all answer the question for what I am trying to. Yes, I am talking about dynamic SQL. As seen by the 3rd and 4th line, I am creating query code on the fly. I use prepared statements, but it will not protect against a later query. – user1032531 May 09 '14 at 12:01

1 Answers1

4

Ahahah, that's just fantastic!
Finally I managed to understand what does this fella mean under his "dynamical sql". No wonder as it's perfectly disguised and looks like ordinary SQL at both first and second glance!

Here goes the answer:

Don't do it. Ever.

Your idea of "dynamical" SQL is essentially wrong. Nobody does it this way.

I don't know your particular task but your solution is apparently wrong. And there is surely a sane way to do it. Just follow these simple rules:

  • get rid of meta_table
  • get rid of SQL queries stored in database
  • write (or build) all your queries in your application from two sources only:
    • hardcoded SQL, pre-written in your code
    • prepared statements for all the variable parts

and have all your SQL perfectly safe

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Ah, an applicable answer. Thank you. My application allows the user to make selections regarding which columns to display, what filters to use (i.e. WHERE clauses), whether to group on something, etc. Based on their selections, sometimes other tables need to be joined. Yes, it is complicated. I am trying to determine how I can do this without using the evil dynamic SQL. Have you ever come across any whitepapers on the subject>? – user1032531 May 09 '14 at 12:21
  • it seems you should give a try for some query builder first. – Your Common Sense May 09 '14 at 12:32
  • Query builder? Sounds like the same thing, but someone else built it and wrapped a class around it. Know of a good one? Maybe http://laravel.com/docs/queries? – user1032531 May 09 '14 at 12:46
  • @user1032531 Yes, this one looks promising. the main benefit is you can really build queries dynamically, keeping all the safety – Your Common Sense May 09 '14 at 14:08
  • Thank you YourCommonSense, I very much appreciate your response, but I don't really feel it answers how to "Preventing SQL Injection in dynamical SQL" (other than don't even try, and instead use someone else's class to do so). I see my original question was closed as another question evidently answered it, however, don't see how the referenced answer is at all relevant. How do you think I should have better asked this question? – user1032531 May 10 '14 at 00:41
  • Yes, you apparently should ask another question. But asking help not with this ridiculous iea of storing SQL in database but with real dynamical queries built on the fly based on user input, providing some real life use case. – Your Common Sense May 10 '14 at 04:34
  • @YourCommonSense this question was marked as duplicate because of the deleted answer below, is it really a duplicate though? Should it be reopened now instead? –  May 10 '14 at 18:26
  • @Cupcake it should be asked over, on the conditions I listed above. It is marked not due to answer but due to questions's extremely cryptic nature - it's hard to recognize a dynamic SQL in it – Your Common Sense May 11 '14 at 05:44