Solved

Issue with ' within variable breaking mysql

Posted on 2011-09-28
13
346 Views
Last Modified: 2012-05-12
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
Comment
Question by:hypervisor
  • 3
  • 3
  • 2
  • +2
13 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36764407
If you use mysqlrealescapestring it will fix it for you
0
 

Author Comment

by:hypervisor
ID: 36765429
Can you provide an example?
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36766614
$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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:hypervisor
ID: 36771509
$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
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36773505
You cant put a quote before mysqlrealescapestring.
0
 

Author Comment

by:hypervisor
ID: 36775237
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
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 36781486
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36788390
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
 
LVL 8

Expert Comment

by:rationalboss
ID: 36813910
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37057831
@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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question