-1

I have a form for a blog post and whenever I have a single quote (for example, that's) the SQL insert code breaks. I have tried using mysql_real_escape_string:

$Description = mysql_real_escape_string($_POST['Description']);

But this doesn't work. I tried using htmlspecialchars() too, but for these posts I need to be able to use HTML code for adding links and images.

I updated the SQL code to like a guide said to do and still I didn't work. Then I can update it if there are no single quotes, so the code does work, but the single quotes are causing lots of trouble

        $SQL = "UPDATE Posts SET Title = '$Title',LinkTitle = '$LinkTitle',MainPicture = '$MainPic',Description ='".$Description."',Maintext = '$Main',Type = '$SubCategory',Featured = '$Featured'
    ,category = '$Category',thumbnail='$thumb'
    WHERE ID = '$id'";

Fix

Thanks to Gaucho for the solution the problem was I was using mysqli to connect to the database. changing the connection code to normal mysql_connect fixed the problem.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Dan Hastings
  • 3,241
  • 7
  • 34
  • 71
  • Why don't you use mysqli? It's much more secure. – Johannes Staehlin Jan 12 '13 at 14:25
  • 1
    It would be better if you used parameterized queries instead. But in any case, you have the bare SQL query in your hands. Print it out and see where exactly it's broken, then you will have an idea of what needs fixing. This is basic debugging. – Jon Jan 12 '13 at 14:26
  • 1
    Post the **exact** error message you get with `echo mysql_error();`. Writing "It doesn't work" is not helpful to anyone. – Jocelyn Jan 12 '13 at 14:27
  • 2
    possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) – Quentin Jan 12 '13 at 14:28
  • 1
    Your title doesn't match your first problem, which is a problem connecting to the database. – Jocelyn Jan 12 '13 at 14:39
  • change the title to "Can't connect to local MySQL server" and vote my answer connecting to your server as i wrote. – Gaucho Jan 12 '13 at 15:04

3 Answers3

0

You should use prepared statements, PDO makes that easy to do. Then you don't need the quotes.

Peter Wooster
  • 6,009
  • 2
  • 27
  • 39
0

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
0

Use this code, with your database name and password, and report the error you're are obtaining.

The mysql_real_escape_string is not the problem in your query since in my case it is working fine. Let us know even your PHP and MySQL version.

 <?php
     // Connect to your database
     mysql_connect("localhost","yourDBuserName","yourDBpassword");

     // Specify database
     mysql_select_db("yourDBname") or die;

     //Build SQL query
     $Description = mysql_real_escape_string("that's amore");
     $query = "select * from Posts where Description ='" . $Description . "'";
     $queryResult=mysql_query($query);
     if (!$queryResult) {
         $message  = 'Invalid query: ' . mysql_error() . "\n";
         $message .= 'Whole query: ' . $query;
         die($message);
     }
     die ($queryResult);
 ?>

I even suggest you PhpEd to debug your code. Note: the result of mysql_real_escape_string in my sample is "that\\'s amore". Any echoed string that doesn't start with Invalid.. means that the query is running fine.

Note 2: this is the right method to connect to your server.

If you want to connect using mySqli, use the following code to connect, since you are doing it in the wrong way:

$mysqli = @new mysqli('yourDBaddress', 'yourDBuserName', 'yourDBpassword', 'yourDBname');

if ($mysqli->connect_errno) {
    die('Connect Error: ' . $mysqli->connect_errno);
}
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Gaucho
  • 1,328
  • 1
  • 17
  • 32
  • i ran this code and the result in the browser was "Resource id #2" the code i have been using to connect to the db has been different to this though. could this be the problem $db = new mysqli(db name, username, pass and table) if(mysqli_connect_errno()) { echo ' error connecting to db'; exit; } – Dan Hastings Jan 12 '13 at 14:52
  • this means you don't have a problem with mysql_real_escape_string. resource id #2 means that the query runs fine. please vote my answer. what you use to connect to your db? correct it according to my sample, then replace my query with your query in my variable $query. – Gaucho Jan 12 '13 at 14:56
  • i have a php file that contains the connection details that i posted above (with correct connection details of course) then on every page that connects to the db i use a php include the file rather than having to add the connection code to every page (i had this at the start and reset the password and had to rewrite every page) – Dan Hastings Jan 12 '13 at 15:01
  • MySQLi is globally considered a failure. my personal suggestion is to don't use it, even if it is reported on php.net that the mysql_connect "..is deprecated as of PHP 5.5.0..." – Gaucho Jan 12 '13 at 15:17
  • Hei @user1889580 , i edited my answer with the right method to connect to mysqli. vote me up!!! – Gaucho Jan 12 '13 at 15:27
  • brilliant that worked i will just hard code the connection details into the 2 pages that use this code for now but it works fine now thank you so much! this was really annoying me – Dan Hastings Jan 12 '13 at 15:30
  • @user1889580 , You're welcome. Could it then be accepted as answer? Remember even to update the title of your question as suggested in your question comments. – Gaucho Jan 12 '13 at 15:32
  • i clicked the tick for it there it wont let me give it a vote up but it is the accepted answer. thanks again :D – Dan Hastings Jan 12 '13 at 15:35