As you can see, people suggest you use prepared statements at the most. It's not wrong, but when your query is executed just once per process, there would be a slight performance penalty.
I was facing this issue, but I think I solved it in very sophisticated way - the way hackers use to avoid using quotes. I used this in conjunction with emulated prepared statements. I use it to prevent all kinds of possible SQL injection attacks.
If you expect input to be integer make sure it's really integer. In a variable-type language like PHP it is this very important. You can use for example this very simple but powerful solution: sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);
If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function called mysql_hex_string(), in PHP you can use bin2hex().
Don't worry about that the escaped string will have a 2x size of its original length because even if you use mysql_real_escape_string, PHP has to allocate same capacity ((2*input_length)+1), which is the same.
This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with 0x or use the MySQL function UNHEX instead.
SELECT password FROM users WHERE name = UNHEX('726f6f74')
Hex is the perfect escape. No way to inject.
There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:
The ** 0x** prefix can only be used for data columns such as char, varchar, text, block, binary, etc.
Also, its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with '', or you'll get an error.
UNHEX() works on any column; you do not have to worry about the empty string.
Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just with mysql_real_escape_string. Then you can avoid the use of quotes.
"SELECT title FROM article WHERE id = " . mysql_real_escape_string($_GET["id"])
an attack can inject you very easily. Consider the following injected code returned from your script:
But if the coder of an injectable site would hex it, no injection would be possible because the query would look like this: SELECT ... WHERE id = UNHEX('2d312075...3635')
@SumitGupta Yea, you did. MySQL doesnt concatenate with + but with CONCAT. And to the performance: I dont think it affects performance because mysql has to parse data and it doesnt matter if origin is string or hex
@YourCommonSense You dont understand the concept... If you want to have string in mysql you quote it like this 'root' or you can hex it 0x726f6f74 BUT if you want a number and send it as string you will probably write '42' not CHAR(42) ... '42' in hex would be 0x3432 not 0x42
@YourCommonSense I have nothing to say... just lol... if you still want to try hex on numeric fields, see second comment. I bet with you that it'll work.
@YourCommonSense you still dont understand ? You cannot use 0x and concat because if the string is empty you will end with an error. If you want simple alternative to your query try this one SELECT title FROM article WHERE id = UNHEX(' . bin2hex($_GET["id"]) . ')