Solved

Issue with ' within variable breaking mysql

Posted on 2011-09-28
13
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 

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 110

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 110

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

717 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