5

I asked this question earlier Preventing SQL Injection in dynamical SQL, but was not clear which resulted in the question being closed, and was later told in the comments that I should ask the question again.

A requirement of my PHP/MySQL application is to allow all users to create their own multiple "views" of the data. These views are not traditional SQL views, but performed by the application.

For instance, the user could create a view and provide the following criteria:

  • Name of the view.
  • Desired records per page.
  • Which columns to display by using a dropdown menu.
  • Any filters for the results. A first dropdown menu is used to select the column, a second dropdown menu is used to select the operator (equals, not equal, greater than, etc), and then either a third dropdown menu is used to select the value to be matched or the user directly enters the value to be matched.
  • Whether the records should be grouped on a given column.

Based on the user's selection, various tables might need to be joined to the query to support the select, where, and group by clauses, and the application is used to eliminate duplicate table joins should they exist.

After the user has finished configuring their views, there is a dropdown menu which allows them to select their desired view, and the appropriate results are displayed.

I have implemented this functionality by storing the user's selections in several tables, and also storing the resultant query (actually, I store individual sections of the query in various columns so I may count the total results in an initial query and return the correct number of results in a second query) in a SQL table. Note that I am storing the user's selections only so that I can allow them to edit their view requirements, and not to create the resultant query on the fly (more on this later).

I recognize that I have to be very careful as doing this can be prone to SQL injunction. For instance, I can't just escape the user's input using PDO and store it in the database, and then later retrieve the data and use it in a query since when it is retrieved, it is no longer escaped.

To combat this risk, I limit user input to integers wherever possible, and typecast them where possible. Where user inputs are percents and dollars, I multiply by 100, typecast the resultant as an integer, and then divide by 100 before storing it. A couple of the filters require text as the value in the WHERE clause, and as stated earlier, escaping the data is not enought, and instead I am using $user_input= preg_replace('/[^a-z0-9,]/i', '', $_POST['user_input']); to make safe.

Is this an accepted method to implement this functionality? Is there an easier way to ensure the user input is safe? My earlier post Preventing SQL Injection in dynamical SQL indicated that this type of practice should never be attempted, however, I don't know how else to do it. Are there any other steps I should take to prevent injection?

Or maybe I should not create a query and store it in a table when the user saves his view configuration, but create the query on the fly using the user's saved values each time the user selects a given view. This would have a negative performance impact and add complicity but I suppose I can do it. Would you recommend using this strategy?

Thank you

Community
  • 1
  • 1
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • Instead of doing all that use COOKIES to store the User Preferences, then read those cookies and display your SQL Data Accordingly. That's What i do. – Tasos May 12 '14 at 18:43
  • 5
    The thing you have to use extensively is called whitelisting. check user input against predefined values, be it column names or operators. After that you can store the resulting query in database. – Your Common Sense May 12 '14 at 19:01
  • Instead of preg_replace are you able to use mysqli_real_escape_string? You could certainly use this multiple times (once when storing the user's query config, again when creating SQL string). – James May 12 '14 at 19:03
  • 1
    @James: `mysqli_real_escape_string` does not prevent injection. – The Blue Dog May 12 '14 at 19:17
  • @YourCommonSense I agree whitelisting is an option, and is what I originally already implemented. Would you recommend whitelisting on a per character basis using preg_replace like I showed (which would convert `user's input` to `users input`, or on a per input basis using something like PHP's `ctype_alpha()` which would return false if not valid? – user1032531 May 12 '14 at 19:42
  • @Tasos. When would the cookies be set? I wish the user's views to exist regardless of what client they are using. What is the value of doing so? – user1032531 May 12 '14 at 19:43
  • @James. Any escaping as far as I know doesn't prevent injection when you are storing SQL in the DB. You are safe when saving the data, but when you retrieve it and execute it, you are vulnerable. – user1032531 May 12 '14 at 19:45
  • @YourCommonSense. Also, any thoughts about instead of whitelisting, save the actual user's inputs using PDO, retrieving the user's inputs, and they executing the select query again using PDO. I can't totally use this approach is some of the user's inputs are used to determine columns to display and even tables to join which is not applicable for PDO and would require whitelisting. – user1032531 May 12 '14 at 19:55
  • @user1032531 -- When the user creates a view and provides the criteria. You store the criteria into Cookies. If you are using multiple views then store a View Id as a cookie too. Infact if you are using multiple views then the critearia plus the view will go into one cookie for example --- View1-Desiredrecords-colmn1,column2,column3-filter1,filter2,filter3-colgroupno,colgroupyes,colgroupno -- then using PHP split the Cookie variable and do whatever you want for your SQl. Its very easy – Tasos May 12 '14 at 20:20
  • @Tasos. Thank you, but I really don't see what the difference is whether stored in a cookie or in a DB other than not having to escape the data when saving it as it which is rather trivial. The real threat as I see it is after retrieving is, using the user data to execute a query. – user1032531 May 13 '14 at 03:46
  • @YourCommonSense. QUOTE "check user input against predefined values, be it column names or operators". These two are easy. What about where clause values? – user1032531 May 13 '14 at 03:51
  • If I get time, I'll write you complete answer – Your Common Sense May 13 '14 at 13:36
  • @YourCommonSense. Thank you, I look forward to it. – user1032531 May 13 '14 at 20:15

1 Answers1

-3

Hello I get you I think.

This is what you are looking for: http://www.php.net/manual/en/function.addslashes.php

$safestring=mysql_real_escape_string($_POST['user_input']);
$safestring=addslashes($safestring);

If you want to make it even more safer, that is the user cannot input html in the input, use this function after using the above one (i.e. mysql_real_escape_string)

$safestring=htmlspecialchars($safestring);

Now all your use input will stay as it is, if string is "user's input" it will stay as "user's input" and not change to "users input", so nothing is being replaced, and its still safe.

Regards.

user1735921
  • 1,359
  • 1
  • 21
  • 46