2

I am beginner in web development, I am developing a site that allows user to post various discussions and others comment and reply on it. The problem I am facing is, the user can post almost anything, including code snippets and any other thing which might possible include single quotes, double quotes and even some html content.

When such posts are being posted, it is intervening with the MySQL insert query as the quotes are ending the string and as a result the query is failing. And even when I display the string using php, the string is being interpreted as html by the browser, where as I want it to be interpreted as text. Do I have to parse the input string manually and escape all the special characters? or is there another way?

halfer
  • 19,824
  • 17
  • 99
  • 186
sasidhar
  • 7,523
  • 15
  • 49
  • 75

5 Answers5

5

You need to read up on a few things

SQL Injection - What is SQL Injection and how to prevent it

PHP PDO - Using PHP PDO reduces the risk of injections

htmlentities

The basic premise is this, sanitize all input that is coming in and encode everything that is going out. Don't trust any user input.

If possible, whitelist instead of blacklisting.

EDIT :

I you want to display HTML or other code content in there, users need to mark those areas with the <pre> tag. Or you could use something like a markdown variation for formatting.

Community
  • 1
  • 1
JohnP
  • 49,507
  • 13
  • 108
  • 140
4

Use PDO, prepared statements and bound parameters to insert / update data, eg

$db = new PDO('mysql:host=hostname;dbname=dbname', 'user', 'pass');
$stmt = $db->prepare('INSERT INTO table (col1, col2) VALUES (?, ?)');
$stmt->execute(array('val1', 'val2'));

Edit: Please note, this is a very simplified example

When displaying data, filter it through htmlspecialchars(), eg

<?php echo htmlspecialchars($row['something'], ENT_COMPAT, 'UTF-8') ?>

Update

As noted on your comment to another answer, if you want to maintain indentation and white-space when displaying information in HTML, wrap the content in <pre> tags, eg

<pre><?php echo htmlspecialchars($data, ENT_COMPAT, 'UTF-8') ?></pre>
Phil
  • 157,677
  • 23
  • 242
  • 245
2

Look at mysql_real_escape_string and htmlentities functions in PHP manual.

You can also read the Security chapter in PHP manual.

Alex
  • 403
  • 3
  • 5
1

To avoid the breaking of queries in database (which means you're not escaping them, leaving big holes for sql injection) you use mysql_real_escape_string($string) on the value before passing it to the query string, enclosing it in quotes also.

Ex. $value = mysql_real_escape_string($value);  // be sure to have an open connection before using this function.
$query = "select * from `table` where value = '".$value."'";

As for displaying in html, you should at least echo htmlentities($string) before outputting it to the browser.

Like echo htmlentities($mystring, ENT_QUOTES)`;

Edit:

To preserve withe spaces, you can use nl2br function (which converts linebrakes to the html equivalen <br />) or go for a little deeper $string = nl2br(str_replace(" ", " &nbsp;", $string));, but html code would look a bit ugly, at least for me

Reference: htmlentities and mysql_real_escape_string. nl2br

Damien Pirsy
  • 25,319
  • 8
  • 70
  • 77
  • Thanks alot. Using htmlentities() the white spaces and the indentation is not being conserved. any ideas on how to handle that? – sasidhar May 31 '11 at 05:41
0

use mysql_real_escape_string. It is a good practice to use this on all user inputs to prevent SQL Injection attacks.

Rasika
  • 1,980
  • 13
  • 19