2

I want to prevent the 1=1 using the mysql_real_escape_string but not sure if im doing it right because I can still perform 1=1. This is my code:

$memberId = mysql_real_escape_string($_GET["memberId"]);
$sql = "SELECT firstName, lastName, dateSent, message, messageId FROM member, message WHERE member.memberId = message.sentFromId AND message.inboxId=" . $memberId . " ORDER BY dateSent DESC;";

Thanks

L2G
  • 846
  • 6
  • 28
user974873
  • 311
  • 1
  • 10
  • 21
  • I've explained the whole matter in [MySQL / PDO / Prepared Statements - All a big jump, a bit overwhelming and a little confusing?](http://stackoverflow.com/questions/8061185/mysql-pdo-prepared-statements-all-a-big-jump-a-bit-overwhelming-and-a-lit) – Your Common Sense Nov 25 '11 at 20:24

2 Answers2

6

mysql_real_escape_STRING() is for STRINGS, not integers. There's nothing in 1=1 that requires escaping, so m_r_e_s() will pass it back unchanged.

if you're dealing with integers, then use integer tools:

$memberID = intval($_GET['memberId']);
Marc B
  • 356,200
  • 43
  • 426
  • 500
3

The only correct way to not have SQL injections is using prepared statements. If you attempt to mitigate using escaping you will fail. If you as a rule never concatenate your queries and always use prepared statements, you have a chance.
It also has the advantage of making your code more readable. And has no disadvantages.

MK.
  • 33,605
  • 18
  • 74
  • 111
  • +1 I agree with MK. but (just to share) my stand on `prepared statements` is with `PDO` and not `mysqli` :) – Nonym Nov 25 '11 at 20:20
  • Re. 'no disadvanteges': How do you create a multirow insert (a.k.a extended insert) using prepared statements? – Mchl Nov 25 '11 at 20:27
  • this is not true. if you treat all your data as strings *as a rule*, you will never fail, save for the LIMIT clause parameters. And prepared statements still not a silver bullet anyway. It won't help for identifiers – Your Common Sense Nov 25 '11 at 20:29
  • @Mchl looks like you are right. Other implementations (e.g. JDBC) have a concept of batch prepared statements, but not PHP, apparently. – MK. Nov 25 '11 at 20:32
  • @Col.Shrapnel I totally do not understand what you just said. – MK. Nov 25 '11 at 20:33
  • ok, in other words. 1. prepared statements is not the only correct way to not have SQL injections. Nothing inherently wrong in in quoting/escaping - it's good if used as a rule as well. 2. sometimes we cannot avoid concatenating and prepared statements won't help us. So it isn't an ideal solution either – Your Common Sense Nov 25 '11 at 20:58
  • @mk: prepared statements can only have placeholders for VALUES. if your DB design requires field/table names be inserted dynamically, you can't use a prepared statement. `SELECT ? FROM ? WHERE ?=?` is not possible, only `SELECT x FROM y WHERE z=?`. – Marc B Nov 25 '11 at 21:25
  • @MarcB yes, of course, but that's a totally different problem and conversation. And then the ids will never be coming in as user inputs, most likely. – MK. Nov 25 '11 at 22:27
  • @MK: I don't know what JDBC does, but it surely is not using any MySQL-native mechanism for that. There simply is no MySQL syntax for multirow prepared inserts (except for creating a statement with specific number of rows, which is kind of ugly) – Mchl Nov 26 '11 at 12:31