Solved

Issue with ' within variable breaking mysql

Posted on 2011-09-28
13
343 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 38

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 38

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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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 38

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
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 …

816 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now