• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Issue with ' within variable breaking mysql

I am performing the following mysql query:

"INSERT INTO mirth SET dob='$dob', 
                              exam_id='$exam_id',
                              namef='$namef', 
                              namel='$namel', 
                              status='$status',
                              tran='$tran', 
                              trans_on='$trans_on', 
                              mrn='$mrn'";

Open in new window


The variable $tran is "The dog's walked over the moon".  The ' in dog's is causing a mySQL error 1064.  How can I fix the insert so the ' in dog's doesn't break the Insert?
0
hypervisor
Asked:
hypervisor
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Aaron TomoskyTechnology ConsultantCommented:
If you use mysqlrealescapestring it will fix it for you
0
 
hypervisorAuthor Commented:
Can you provide an example?
0
 
Aaron TomoskyTechnology ConsultantCommented:
$query = mysql_real_escape_string(your query here);
Then send the query however you do now. You didn't gve enough code for a more specific example.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hypervisorAuthor Commented:
$query="mysql_real_escape_string(INSERT INTO mirth SET dob='$dob', 
                              exam_id='$exam_id',
                              namef='$namef', 
                              namel='$namel', 
                              status='$status',
                              tran='$tran', 
                              trans_on='$trans_on', 
                              mrn='$mrn')";

Open in new window


result in this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql_real_escape_string(INSERT INTO mirth SET dob='1974-06-09',
             ' at line 1
0
 
Aaron TomoskyTechnology ConsultantCommented:
You cant put a quote before mysqlrealescapestring.
0
 
hypervisorAuthor Commented:
Should it look like this, then?

$query=mysql_real_escape_string(INSERT INTO mirth SET dob='$dob', 
                              exam_id='$exam_id',
                              namef='$namef', 
                              namel='$namel', 
                              status='$status',
                              tran='$tran', 
                              trans_on='$trans_on', 
                              mrn='$mrn');

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
Here's the PHP page for http://us3.php.net/manual/en/function.mysql-real-escape-string.php .  You should not be using mysql_real_escape_string on the whole query, just on the individual variables.  You need the punctuation in the query for it to work properly.
0
 
Ray PaseurCommented:
Apply this function to all the variable strings used in the query.
http://php.net/manual/en/function.mysql-real-escape-string.php

Also, consider hiring a professional developer or at least getting this book and giving yourself a month or two to work through the examples.  It will give you something of a foundation in the way PHP and MySQL work together.  It is very readable and comes with a code library that you can download and modify for your own uses.
http://www.sitepoint.com/books/phpmysql4/

An example showing how to do some of the frequently-used actions in PHP and MySQL is in this code snippet.  Hopefully, the man page references will be useful for you, too.   Best of luck with your project, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username' LIMIT 1";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0
 
rationalbossCommented:
Use only mysql_real_escape_string() for the text input, not the whole query. For instance:
$query = "SELECT * FROM mirth WHERE namef='" . mysql_real_escape_string($name). "'";
0
 
Ray PaseurCommented:
@hypervisor, you accepted the wrong answer.  I'll ask a moderator to re-open the question.

The correct answer is from DaveBaldwin at ID:36781486
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now