1

So I have a system where admins can find a row by searching for a user-entered string and selecting in which column to search in.

Obviously, if I have an INT column id=0 in a few rows, they will all pop out in the results whatever the string as 'bob' will become 0 when cast to an INT, so

WHERE id = 'bob'

Will become

WHERE id = 0

So I got plenty of results that don't make sense.

I don't think there is a way to stop mysql from stopping implicit casting (besides it might break other things somewhere else in the site), but is there another solution to avoid this, knowing that the selected column to search in could either be a INT or a STRING? Or is there a way to detect incompatible types and return an empty result set without executing the query?

NaturalBornCamper
  • 3,675
  • 5
  • 39
  • 58
  • You can find the type in `information_schema.columns` and use that information in your application to construct the query or reject the input all together. – Gordon Linoff Feb 28 '15 at 17:10
  • I know that changing identifiers is usually a no-go, but might it be an option to get rid of the `0` values? E.g. by making these columns start at `1` or replacing `0` with `null`? (edit: of course that will only work for this exact case, Gordon's attempt looks more feasible for general cases). – Marvin Feb 28 '15 at 17:24
  • Maybe MySQL will issue a warning in these cases. I think this might worth a try: http://stackoverflow.com/questions/47589/can-i-detect-and-handle-mysql-warnings-with-php – Cem Kalyoncu Feb 28 '15 at 17:26
  • The warning is: `1292 | Truncated incorrect DOUBLE value: 'bob'`. – Rick James Mar 01 '15 at 01:06
  • Yeah I had thought of something similar to that Gordon but had hoped it could be an easy fix. Thanks everybody else for your suggestions but I'd go with Gordon's as it's the most flexible with what I got to do and with minor changes. Gordon, can you write that in form of an answer so I can accept it? – NaturalBornCamper Mar 01 '15 at 01:57

0 Answers0