0

I'm making a quotes and phrases site. I want to post this quotes and prevent <script> or similar <html tags>, I'm currently using the following query to post and push this data to the database

mysql_query("INSERT INTO `phrases` 
    (`id`, `text`, `date`, `views`, `ip`, `reported`, `strange`, `lang`) 
    VALUES (NULL, '$quote', '$date', '0', '$ip', '0', '0', 'en')
");

using the following PHP code

<?php
$date = date('Y-m-d H:i:s', time());
$quote = $_POST["quote"];
$ip = $_SERVER['REMOTE_ADDR'];
//The query above
?>

But I get the following problems:

  • COMPLETED: Timestamp is not showing correctly.

  • I'm getting some unescaped characters.

    For example, I'm trying to post this: 

         <Ω∑©√ß µ„…–å∫∂ƒ™¶§ ~{}œæ€®†¥  øπ[]

    • If I post this as is, it posts

      <Ω∑©√ß µ„…–å∫∂ƒ™¶§ ~{}œæ€®†¥  øπ[]

    • If I use mysql_real_escape_string($quote), it posts 

      <Ω∑©√ß µ„…–å∫∂ƒ™¶§ ~{}œæ€®†¥  øπ[]

    • And if I use htmlspecialchars(nl2br(stripslashes($quote))), it posts 

      &lt;Ω∑©√ß µ„…–å∫∂ƒ™¶§ ~{}œæ€®†¥  øπ[]

  • I've been warned that this is vulnerable to SQL injection

How can I prevent this?

As additional information, this is called via AJAX.

Luis
  • 1,067
  • 1
  • 14
  • 25
  • 3
    $quote is vulnerable to sql injection. – rook Mar 11 '12 at 20:54
  • @Rook how can I prevent this? – Luis Mar 11 '12 at 20:55
  • Don't do that complicated chain of functions to `$quote` when saving it. Use `mysql_real_escape_string()` and HTML-escape it only when you output the value. – JJJ Mar 11 '12 at 20:56
  • `$quote = mysql_real_escape_string($quote);` after doing the first qutoe assign – Tim Mar 11 '12 at 20:58
  • 1
    To avoid SQL Injection, you need to use prepared statements with PDO (PHP Data Objects). Here is a couple examples: http://www.phpeveryday.com/articles/PDO-Insert-and-Update-Statement-Use-Prepared-Statement-P552.html – Jeremy Harris Mar 11 '12 at 21:02
  • This may be unrelated about your question but here is a good answers about sql-injection: http://stackoverflow.com/q/60174/1241723 , maybe you want to read. – draconis Mar 11 '12 at 21:39

1 Answers1

2

These should do what you want.

On input:

$date = date('Y-m-d H:i:s', time());
$ip = $_SERVER['REMOTE_ADDR'];
$quote = mysql_real_escape_string($_POST["quote"]);

On output:

echo nl2br(htmlspecialchars($row["quote"]));
J. Bruni
  • 20,322
  • 12
  • 75
  • 92
  • Your issue is not "timestamp" and not "security" anymore... your issue is "encoding" now. The code above is correct, and you should use the `input` code BEFORE entering the data in the database, only. And you should use the `output` code BEFORE displaying the "quote" column content back to the browser. These are the correct places/moments to apply these snippets. – J. Bruni Mar 12 '12 at 19:11
  • Now, regarding **encoding**, which is your real issue now: if you want to play nice with these strange characters, my suggestion is that you make **everything UTF-8**. And this "everything" is indeed a lot of things, and you need to take care of them all: 1) Your HTML contents, 2) The encoding of your source code files, 3) The database tables and columns, 4) The database connection - this is the basic. Each one is very important. – J. Bruni Mar 12 '12 at 19:14
  • 1
    for (1) you should specify UTF-8 at both a `` tag and a HTTP header (using PHP's `header` function); for (2) you need to check in which character set your source code files are being saved (use your text editor for this) - save them as UTF-8; for (3) you need to check your database tables definitions, and set the character set to UTF-8; finally, for (4) you need to specify UTF-8 as the character set for the communication between PHP and MySQL (the database connection) - the MySQL command is `SET NAMES` – J. Bruni Mar 12 '12 at 19:19
  • Read these two articles, specially the second: http://kunststube.net/encoding/ and http://kunststube.net/frontback/ – J. Bruni Mar 14 '12 at 02:22