-1

Whenever I'm trying to insert data that has an apostrophe in it, I receive a

mysql syntax error

Example text in textarea : I'm an alien will throw a syntax error when I try to insert in the database. I'm using real_escape_string and still is not working.

query snippet 

$text=mysqli_real_escape_string($db,stripslashes(strip_tags(trim($_POST['text']))));

markup

<textarea name='text' placeholder="Type your message here... " value=''></textarea>
Dharman
  • 30,962
  • 25
  • 85
  • 135
Sebastian Farham
  • 815
  • 2
  • 13
  • 27
  • How does your sql script look like? Are you using parameterized query? – Chetan Jun 04 '17 at 03:53
  • @ChetanRanpariya: looks something like this : $updateText = "UPDATE table SET text='$text' WHERE user='$id'"; – Sebastian Farham Jun 04 '17 at 03:55
  • Using parameterized query should resolve this issue. Or may be you can replace single quote with double quote before appending it to the query. – Chetan Jun 04 '17 at 03:57
  • While parametrized queries are the better solution, `mysqli_real_escape_string` should prevent apostrophes from causing problems. What do you see if you `echo $updateText`? – Barmar Jun 04 '17 at 04:12
  • 1
    `mysql syntax error` is not an error message that comes from MySQL. What is the full error message that comes from `mysqli_error($db)`? – Barmar Jun 04 '17 at 04:14
  • Does this answer your question? [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Dharman Nov 14 '21 at 19:41

2 Answers2

2

Just use parameterized queries. Then you don't have to do any escaping, and apostrophes will never be a problem.

It's really easy:

$text=trim($_POST['text']);

$stmt = $pdo->prepare("UPDATE table SET text=? WHERE user=?");
$stmt->execute([$text, $id]);

Using stripslashes() and striptags() should be unnecessary, too.

You can also do prepared statements with parameters with Mysqli. But you should use PDO.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • He's using mysqli, not PDO. – Barmar Jun 04 '17 at 04:11
  • @Barmar: He actually has the right concept but I had to look for a mysqli version of his answer so I will post it. – Sebastian Farham Jun 04 '17 at 04:15
  • 2
    @Barmar, I've decided I'm going to encourage people to use PDO over Mysqli. The only reason I'd support using Mysqli is that it's easier to convert legacy PHP code that used ext/mysql. – Bill Karwin Jun 04 '17 at 04:51
  • 1
    I also prefer PDO over mysqli. But answers are not the appropriate place for software recommendations,they should be in comments. – Barmar Jun 04 '17 at 05:07
  • And if you're going to recommend PDO prepared queries, use named placeholders instead of `?` – Barmar Jun 04 '17 at 05:08
  • 1
    @Barmar, PDO supports both named and positional placeholders. In this example, it's simpler to do it that way. – Bill Karwin Jun 04 '17 at 06:21
1

I had to use parameterized queries. This did the tricK:

 //get variables
 $id = $_POST['id'];
 $text=trim($_POST['text']);

 $sqlText= "UPDATE table SET text=? WHERE user=?";
 $updateText = $db->prepare($sqlText);
 $updateText->bind_param('si',$text, $id);
 $updateText->execute();
Sebastian Farham
  • 815
  • 2
  • 13
  • 27