-2

How can I allow special characters like " ' \ / : ; etc without open up for SQL injection using the code below:

$opendb = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname);

$text = $_POST['text'];

mysql_query("UPDATE table SET text='" . $text . "' WHERE 
id='" . $_GET['id'] . "'");

mysql_close($opendb);

$text contains a sentence from a HTML textarea. When I tries to enter text in a quote it just insert the text before the quotes.

plain jane
  • 1,009
  • 1
  • 8
  • 19
Treps
  • 780
  • 3
  • 12
  • 28
  • 1
    You need to use prepared statements, look here http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Sergej Panic Sep 12 '13 at 11:57
  • Please note the column **Related** on the right side of the page. Trust me, you re not a first one who come across such a question. – Your Common Sense Sep 12 '13 at 11:57

2 Answers2

-2

Well, maybe the simplest solution is to use mysql_real_escape_string() function like this:

$opendb = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname);

$text = $_POST['text'];

mysql_query("UPDATE table SET text='" . mysql_real_escape_string($text) . "' WHERE 
id='" . $_GET['id'] . "'");

mysql_close($opendb);

Edit: using this code you could allow special characters in $text variable to be saved into the database.

You should escape $_GET['id'] also.

eroteev
  • 620
  • 1
  • 7
  • 17
-2

Prepared statement

This would be the safest way to go about doing this. Check out this link for more: How can I prevent SQL injection in PHP?

You might also need to turn off magic quotes, depending what PHP version you are running.

<?php

if( isset($_POST['text']) && isset($_GET['id']) && 
    is_int($_GET['id']) && $_GET['id']>0 ){

     $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

     /* check connection */
     if (mysqli_connect_errno()) {
         printf("Connect failed: %s\n", mysqli_connect_error());
         exit();
     }

     $query = 'UPDATE table SET text = ? WHERE id = ?';

     /* prepare your statement safely */
     $stmt = $mysqli->prepare($query);

     /* bindes variables after statement is prepared */
     $stmt->bind_param('si', $_POST['text'], $_GET['id']);

     /* execute prepared statement */
     $stmt->execute();

     /* close statement */
     $stmt->close();

     /* close connection */
     $mysqli->close();
}else
     echo 'Error: ID and/or Text are invalid';
?>
Community
  • 1
  • 1
Arian Faurtosh
  • 17,987
  • 21
  • 77
  • 115
  • @Treps asked how can he allow special characters "using the code below". You did not use his code, so not answering his question. Furthermore your code is wrong. Did you consider that $_GET['id'] could be something like 'si3920sd923'? If this is the case your code will not work at all. – eroteev Sep 13 '13 at 11:14
  • @StefanEroteev it's obviously that id is the primary key, but if it's not he can just remove is_integer... And change bind to 'ss'. Using his code, you can't be sure that SQL Injection won't be a problem. The safest way to do this is prepared statements. Using escape string function, isn't a good solution. I used all the same variables as he did in his code, he also mentioned in his question comments that he would look into prepared statements, so I showed him one using his variables. I don't understand, What do you have against prepared statements? – Arian Faurtosh Sep 13 '13 at 16:08