2

I know, you should always use prepared statements..., and that's what I do, But! they are slower and if there is some input that's 100% safe I guess I could not use them.

(PHP)

if ( ctype_digit($_POST['id']) ){

 $int_id = (int)$_POST['id'];

  if ( is_int($int_id) ){

   $query = "SELECT id FROM ids WHERE id = {$int_id}";

  }

}
Vixxs
  • 585
  • 6
  • 20
  • http://stackoverflow.com/questions/732561/why-is-using-a-mysql-prepared-statement-more-secure-than-using-the-common-escape – Beginner Jan 17 '17 at 07:10
  • 1
    `ctype_digit` and cast to `int` is enough here. There is no point in use both `ctype_digit` and `is_int`. First one will be true only if all characters in given string are digits – Patryk Uszyński Jan 17 '17 at 07:14
  • But adds an extra of false security, to support my decision of not use prepared statements. – Vixxs Jan 17 '17 at 07:23
  • 1
    what a foolish decision to support. – Your Common Sense Jan 17 '17 at 07:24
  • you *could* not use them. but it's best to use them whenever possible. the overhead, if there is any, is negligible, and it's best practice. if you absolutely *don't* want to use them, there are just a few cases that are reasonably secure. **IMHO** one of them is casting to int, another is using a whitelist for strings. but again: prepared statement are the **best** and cleanest way. (also: whenever in doubt about PDO, listen to @YourCommonSense ;) ) – Franz Gleichmann Jan 17 '17 at 07:25

1 Answers1

0

Speaking about this particular silly example, it is, although redundant, is technically safe.

But you have to understand that our code is not always limited to such a spaghetti code example.

  • First, in a properly designed application a database interaction is separated from input validation. And a database layer just have no idea whether is sent variable is "safe" or not. As a result, it's just much simpler to treat every single variable as "unsafe" and treat all variable uniformly.
  • Second, imagine there is not just a single silly variable, but a dozen of them. How long your code will become?
  • if you plan to use different approaches (prepared and non-prepared ) intermixed, it would still make no sense - it will overcomplicate your code for no reason. Why not to use an uniform solution all the time?

Besides, your premises are simply wrong. Prepared statements are not slower.

Franz Gleichmann
  • 3,211
  • 4
  • 21
  • 29
Your Common Sense
  • 154,967
  • 38
  • 205
  • 325
  • Whenever I have done tests, prepared statements have been slower, the query I want to use here is 100% slower tested right now, I do not know why can it be, but in theory they have to be slower at all times unless you run multiple lines, which is not the case. – Vixxs Jan 17 '17 at 07:57
  • Then, first in my improperly designed application, database interaction is inside the code, second the real case is as silly as the example, just one integrer, I use prepared statments for any POST/GET input, but for this type of case, only this ones, one integrer, I'm thinking not to. Then my application has already prepared and non-prepared intermixed, because in all my testing prepared where always slower in one line executions, so for "inside" safe queries I do not use them. – Vixxs Jan 17 '17 at 07:57
  • So you're looking not for the proper practice, but for the excuse to keep with your bad approach. Don't flatter yourself, you will never get one. – Your Common Sense Jan 17 '17 at 07:59
  • What I loking for is efficiency, for this particular query efficiency is key, some of you maybe dont work with hight traffic applications, where the benefits have a high relation with the efficiency, but here more effisient is more money, this one is one of the most used queries. So my point of view is that the important things are client satisfaction, benefits, and security, then the programmer has to throw himself into the mud, to maximize those keys I mentioned. So is the query faster = yes, (+ money, faster data delivery to the user), is it as safe? apparently yes, thats I'm asking. – Vixxs Jan 17 '17 at 08:10
  • This "efficiency" thing you are talking about is but a delusion. You will never notice any improvement making some of your queries non prepared. Looking at your question record, I notice that you are constantly asking for the excuse for the bad practices. In fact, following this road you'll end up with drastically inefficient application, which will be revealed at the time when it will reach the high traffic level. – Your Common Sense Jan 17 '17 at 08:21
  • I really thing you are wrong when you say "prepared statements are not slower", they are faster if you have multiple ejecutions, but not for one, there is one step more in the process, it may to be slower. Anywhere I look on the internet it says that for 1 execution. https://en.wikipedia.org/wiki/Prepared_statement "if a query is executed only once, server-side prepared statements can be slower because of the additional round-trip to the server", and this is happening in my application, and I overpose efficiency to good practise as long as it's safe, yes. – Vixxs Jan 17 '17 at 08:34
  • You have been told already that the difference, if there is any, is negligible. Having no idea of the real efficiency, you are trying to improve the part of your application that will never be a bottleneck. Yet, being busy with such trifle things, you are of course overlooking the parts that a really slow. Which will be revealed when your application will get a real traffic. – Your Common Sense Jan 17 '17 at 08:38
  • It's not that i'm proud, it's just that i have to do this things, I have to cut corners. You are right, there are lots of other things to care about, I would say that in my app queries are one of the most important but let's leave it there. So it all comes down to is it significantly slower or not, as I said in the tests that I've done I see they are, but you said thats a "delusion". So I will try to make something that can be shared and checked. If you know some way I can do that. – Vixxs Jan 17 '17 at 08:58
  • And since you where talking about other questions, I didn't do the "comma separated id", I asked, it was worse, I tested it was worse, end of the story. Not same thing here. I haven't seen with my eyes that it's worse, I'm trying but I haven't been able to – Vixxs Jan 17 '17 at 09:12
  • Surely I do. there are applications like Apache benchmark (bundled with apache web server) or Siege that do test your web-server simulating any level of traffic you want. thay could be used to test your app's performance as well. If you'll be able to notice any difference that exceeds the measurement error using this method, then your assumptions are true. Otherwise you are just wasting your time. – Your Common Sense Jan 17 '17 at 09:29
  • You where right that is almost negligible* (for the overall page load), with Apache bench "-n 1000 -c 2" the difference is an honest 2%, and it's a simple page, prints a short json array. There is no isolation in this 2%, so in the test the page is doing multiple actions and others queries. But there is not anything I can do globaly, just cut here and there, and the fact is that if I isolete the query with a loop, no fetching, including binding for the prepared the difference it goes over 100%. – Vixxs Jan 17 '17 at 11:03
  • So since all the drawbacks commented in you answer my app already have, and it's safe I think I'm gonna cut this 2%. If it wasn't the case, or it was a more complex page and the % was even lower, or was not one of the most required actions, I probably would not. Maybe there is something wrong in the difference of 100% with isolation, I will have I look at it, but right now it is what it is. – Vixxs Jan 17 '17 at 11:08
  • I can give your answer as good, if you change "prepared statements are not slower" to something like, prepared statements are faster for multiple executions but may be slower for just one execution, but usually it is not significant on the overall page load. Only if you feel this is a true statment, if not, no need to argue me, go change that on the wikipedia. – Vixxs Jan 17 '17 at 11:23