0

Possible Duplicate:
Mysql Real Escape String PHP Function Adding “\” to My Field Entry

So here's the deal. I have a local server and a remote server and they both run the same php file which connects to 2 identical mysql databases. On the local server, the command

mysql_query("INSERT INTO eventtypes (name) VALUES ('".addslashes($_GET['name'])."')")

works like a charm, and does not insert the slashes into the field, only the raw value of the $_GET['name']. On the remote server however, the slashes are being inserted too. I have tried these:

mysql_query("INSERT INTO eventtypes (name) VALUES ('".str_replace("'","''",$_GET['name'])."')

This one returns false, although when I run the exact returned string on the command line of the corresponding database, it properly inserts the right amount of single quotes, which is super-weird.

mysql_query("INSERT INTO eventtypes (name) VALUES ('".mysql_real_escape_string($_GET['name'])."')")

mysql_real_escape_string() only added slashes to the single quotes, which were also inserted like with addslashes().

Community
  • 1
  • 1
user1656350
  • 3
  • 2
  • And you don't want to add slashes to the single quotes? I really don't understand what are you trying to do, your titlw states "escaping single quotes" at the end you say `mysql_real_escape_string` only adds to single quotes. Bottom line: don't use `mysql_*` functions as they are deprecated. – Mihai Iorga Sep 08 '12 at 08:13

3 Answers3

2

It sounds like you have Magic Quotes enabled on the remote server. This feature is causing the inconsistency you have discovered. Turn it off.

Once you have fixed that problem, the best approach of the three you are trying is mysql_real_escape_string. However, that is a legacy approach and not recommended. Use a modern database library (such as mysqli or PDO) and bound arguments. See also Best way to prevent SQL Injection in PHP.

Community
  • 1
  • 1
Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
  • 1
    Who downvoted this? It's the only answer which actually answers the question! Even with PDO, you **will** have the `\ ` inserted into the DB if you don't disable the Magic Quotes. – Fabrício Matté Sep 08 '12 at 08:18
  • While all answers were helpful, this was the info I was looking for, God bless you... – user1656350 Sep 08 '12 at 08:31
1

Stop using mysql_query.

Use PDO and parameterized queries.

$pdo = new PDO($dsn, $username, $password);
$statement = $pdo->prepare("INSERT INTO eventtypes (name) VALUES (:eventype)");
$statement->execute(array('eventtype' => $_GET['name']));
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Benjamin Diele
  • 1,177
  • 1
  • 10
  • 26
  • 1
    you realize that query won't work .. right ? Remove single quotes on :eventype – Mihai Iorga Sep 08 '12 at 08:17
  • 1
    While this is good advice, this won't solve the problem of the inconsistent slashes in the data described in the question. – Quentin Sep 08 '12 at 08:18
  • The `->execute()` call was still wrong. Use an associative array for named parameters. (I'm unhappy with both PDO answers here so far. Yours using unneeded parameter naming *for one value*, and the other an even more long-winded `->bind_param()`, ensuring newcomers will keep avoiding PDO over the shorter mysql approach.) – mario Sep 08 '12 at 08:25
  • @mario Thanks for that. Got any suggestions to making PDO easier to explain? – Benjamin Diele Sep 08 '12 at 08:33
0

You're code is prone to SQL Injection. The best method is to use PDO or MYSQLI

Example of using PDO extension:

<?php

    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    $stmt = $dbh->prepare("INSERT INTO eventtypes (name) VALUES (?)");
    $stmt->bindParam(1, $_GET['name']);
    $stmt->execute();

?>

this will allow you to insert records with single quotes.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    While this is good advice, this won't solve the problem of the inconsistent slashes in the data described in the question. – Quentin Sep 08 '12 at 08:19