FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Get error on ' and " when executing mysql statement





Philip
see on subject topic

example
$test= 'ad"belto';
mysql_query("select * from class where name =$test ");

how do i drive it ?
$test is a variable that a user can change the value. Crying or Very sad


johanfh
I'm not good at php, just at Perl. But in that language you can't just put a quote (") in a scalar. You have to put a \ before it.
so:
$test = 'ad"belto';

would be:
$test = 'ad\"belto';

does that help?
JohanFH
AftershockVibe
You example should actually work until you concatenate it with another variable which presumably also contains double quote marks for the SQL. For simplicity you can just use the addslashes() function.

For example;
Code:
$test = addslashes('ad"belto');


If you are validating user input it is probably safer and better to use mysql_real_escape_string() however. This works in the same way but removes even more characters which can cause problems with MySQL queries.

Manual Page:
http://uk.php.net/manual/en/function.addslashes.php
or
ttp://uk.php.net/manual/en/function.mysql-real-escape-string.php
hexkid
Philip wrote:
$test= 'ad"belto';
mysql_query("select * from class where name =$test ");


The query will be
Code:
select * from  class where name =ad"belto   (with a space at the end)

This query is not valid SQL!

to johanfh: you can have quotes (single or double) inside a string; no need to escape it

To make it a valid SQL you need to enclose the $test in quotes
Code:
select * from  class where name = 'ad"belto'


Now, you ask: "What if the user entered a single quote?"
Ah! Then you have to have a little more work. This is the way I like to do this type of thing
Code:
<?php
// assume one single connection was made to the SQL server
// and a database selected
$user_input = 'stuff with \x27 (single quote) and " (double quote)';
$sql = "select <FIELDS> from class where name = '" . mysql_real_escape_string($user_input) . "'";
$res = mysql_query($sql) or die('Error in query [' . $sql . ']. The error was: ' . mysql_error());
while ($row = mysql_fetch_row($res)) {
  // ...
}
mysql_close();
?>
Philip
so i just need to replace like this one ?

$test= mysql_real_escape_string('ad"belto');
mysql_query("select * from class where name ='".$test."'");

kv
This will do

Code:


$test= mysql_real_escape_string('ad"belto');
mysql_query("select * from class where name ='$test' ");



Looks more neater.
Rhysige
And although the problem has been answered I would just like to point out a few things most people lack when writing querys, it just helps people read and in cases prevents errors.

Code:

$test= mysql_real_escape_string('ad"belto');
mysql_query("SELECT * FROM `class` WHERE `name` ='$test' ");
Philip
Rhysige wrote:
And although the problem has been answered I would just like to point out a few things most people lack when writing querys, it just helps people read and in cases prevents errors.

Code:

$test= mysql_real_escape_string('ad"belto');
mysql_query("SELECT * FROM `class` WHERE `name` ='$test' ");


so like this one will work huh

$test= mysql_real_escape_string('ad"belto');
mysql_query("SELECT * FROM `class` WHERE `name` ='$test' ");

ranzon
Philip wrote:
Rhysige wrote:
And although the problem has been answered I would just like to point out a few things most people lack when writing querys, it just helps people read and in cases prevents errors.

Code:

$test= mysql_real_escape_string('ad"belto');
mysql_query("SELECT * FROM `class` WHERE `name` ='$test' ");


so like this one will work huh

$test= mysql_real_escape_string('ad"belto');
mysql_query("SELECT * FROM `class` WHERE `name` ='$test' ");



why all the ` ?? use this instead:

$test= mysql_real_escape_string('ad"belto');
mysql_query("SELECT * FROM class WHERE name='$test' ");
krazycapital
Some people add ` to keep the code easier to read. Expceially when using defines. Example (without):
Code:
mysql_query("SELECT * FROM `table` WHERE `id`='define1',`id2`='define2' LIMIT 5");

Without, this is what it'd look like:
Code:
mysql_query("SELECT * FROM table WHERE id='define1',id2='define2' LIMIT 5");

I find the first easier, cause they aren't always named like that either.
Philip
WHERE id='define1',id2='define2' <-- what is that mean ?
iam ussulay uses where id='define1' and/or id2='define2'

what did u mean by using , ?
Related topics
MySQL statement error (got me going)
Inserting data to MYSQL with a PHP script
Indigoperl includes a copy of Apache 2.0.44, PHP 4.2.3, perl
Shout Box
Creating a Search Script with PHP for MySQL
Question about a mySQL statement
[RESOLVED] PHP Error
Mysql
error with MySQL
mysql in xampp installation problem
Error with MySQL and PHP script in my CMS
Validating sql statements
General error - mysql server has gone away.
Perl Error Message
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.