2

Possible Duplicate:
Are dynamic mysql queries with sql escaping just as secure as prepared statements?

Is using only mysqli_real_escape_string enough to secure a query? Or is there more to consider when trying to securely query a database?

Community
  • 1
  • 1
Jay
  • 10,831
  • 9
  • 26
  • 33
  • @irc I *was* working on it. Might as well use your answer now since it's just what I was going to say. – NullUserException Mar 04 '11 at 22:43
  • You could use prepared statements. Follow that link on ircmaxwell's comment. – NullUserException Mar 04 '11 at 22:44
  • Can you show an example of what you are doing? – Pekka Mar 04 '11 at 22:46
  • @Null: If you expanded, I would have deleted my comment. Just that if you had planned on leaving it like that (which I didn't know at the time), it was kind of pointless. But I understand why you did it ;-)... – ircmaxell Mar 04 '11 at 22:46
  • @irc I wouldn't leave it like that. I haven't used SO in a while... It seems like the dynamics of the site have changed a bit. I used to key in a short answer as fast as I could and even then chances are somebody would beat me to it. – NullUserException Mar 04 '11 at 22:49
  • Ok, I guess there are already a lot of topics about that function and securing query: http://stackoverflow.com/search?q=mysqli_real_escape_string+secure – Ernest Mar 04 '11 at 22:54

1 Answers1

2

If used everywhere correctly real_escape_string is an option. But consider the following code:

$page = $_GET['page'];
$sql = 'SELECT `name` FROM `user` WHERE `id` = ' . mysqli_real_escape_string($page);

Safe or not? real_escape_string can only be used to escape strings inside quotation marks. $page could be 1 OR id IN (2,3,4,5,6,7,8,9) → no quotation marks, no real escaping. Casting to the correct datatype (int) might help in this case. You're better off using prepared statements, they are not as easily to mis-use.

KatharinaSt
  • 73
  • 1
  • 7
knittl
  • 246,190
  • 53
  • 318
  • 364
  • 1
    Which is why you should never *escape* an integer. You should always either cast, or enforce the type explicitly... – ircmaxell Mar 04 '11 at 22:53
  • Do the `mysqli_real_escape_string()` function not normally need the connexion to database argument ? – TOPKAT Apr 28 '16 at 15:17
  • 1
    @SébastienGarcia-Roméo: by default it will use the currently active connection (I think that's the one opened most recently) – knittl Apr 28 '16 at 16:26
  • What if I forcefully input all my data as quoted string no matter what the data type it should be, in that case ```id` = '1 OR id IN (2,3,4,5,6,7,8,9)'`` condition will fail – Aun Rizvi Aug 16 '16 at 11:43
  • @AunRizvi: I don't understand your comment. I think your mardown is off (code is not highlighted properly). Who inputs the data? Who's quoting the data? Which condition will fail? – knittl Aug 16 '16 at 12:05
  • @knittl consider the query in your given answer, if I keep the value of id in quotes, this will make the value as string and yet it will work fine with integers as string, but I will be safe from SQL Injections by using real_escape_string, can I use such practice to overcome the integers problem using this function? – Aun Rizvi Aug 16 '16 at 15:04
  • 2
    @AunRizvi: automatic type conversion is a very dirty workaround. I don't like it. I don't know about the performance implications, and many other DBMSs won't allow that. – knittl Aug 16 '16 at 20:10