Solved

Issue with ' within variable breaking mysql

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

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 82

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 108

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 108

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

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
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 dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

757 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

23 Experts available now in Live!

Get 1:1 Help Now