PHP and quote

[D1R]

I have to migrate a web application written in PHP and I am using
PHP 5.2.6 ISAPI.

The problem I have is related to some sql queries that have the
following format:

$sql = "insert into table_name values ('','" . $value1 . "','" .
$value2 . "',')";

This fails because PHP will translate this in something like:

Insert into table_name values (", '10','15')
In other words if I have in my script something like '10' that will
translated into
the same '10' but something like '' will make the final query string
to look different
and to fail.

This script run without problems in the old server and I guess should
be something
in PHP.ini but I cannot find what is different.

Is there a setting in php.ini that can fix this?

Thank you!

[C. (http://symcbean.blogspot.com/)]

On 21 Aug, 22:43, D1R <[e-mail]:

I have to migrate a web application written in PHP and I am using
PHP 5.2.6 ISAPI.

The problem I have is related to some sql queries that have the
following format:

$sql = "insert into table_name values ('','" . $value1 . "','" .
$value2 . "',')";

This fails because PHP will translate this in something like:

Insert into table_name values (", '10','15')
In other words if I have in my script something like '10' that will
translated into
the same '10' but something like '' will make the final query string
to look different
and to fail.

This script run without problems in the old server and I guess should
be something
in PHP.ini but I cannot find what is different.

You've totally failed to isolate the problem but offerd various bits
of irrelevant information - the example you've provided gives no
indication of where the data might have come from, and the fact that
you are creating an SQL query (badly) has nothing to do with the
problem. Taking a random stab in the dark, it looks like a scope
problem - your old server may have had register_globals on but your
new one doesn't (which would also indicate that your code is badly
structured).

Read this:
http://www.catb.org/~esr/faqs/smart-questions.html

and this:
http://www.php.net/register_globals

and this:
http://en.wikipedia.org/wiki/Structured_programming

C.
C.

[Dale]

"D1R" <dl****@gmail.comwrote in message
news:cd**********************************@b1g2000h sg.googlegroups.com...

>I have to migrate a web application written in PHP and I am using
PHP 5.2.6 ISAPI.

The problem I have is related to some sql queries that have the
following format:

$sql = "insert into table_name values ('','" . $value1 . "','" .
$value2 . "',')";

This fails because PHP will translate this in something like:

Insert into table_name values (", '10','15')

that's a false conclusion. the " in the line above is really two tics...not
a single quote. that's the first misleading premise.

second, i can only say that this is more likely to be based on the database
you are using. in oracle, '' is the same thing as NULL. in mysql, ms sql
server, et. al. '' is merely an empty string. so, if you're using oracle and
the first field in table_name is set as NOT NULL, then your insert will
fail...because of the db, not php. there could also be constraints and
triggers put on the table that won't allow you to insert NULL *or* a blank
string into the first field.

it's hard to tell you anything unless you post the actual error. and, i
don't like assuming anything.

cheers.

[D1R]

Thank you for your fast answer.

I use to write code in PHP but I am not an expert so I did not
provide enough information. Sorry about that.

The code was written by somebody else and it is using MySQL as DB.
I had to migrate the web server and that was the time when I start
having problems and I discover that some options did not work.
The code I was talking about is:
$SQL = "Insert into tablename values('','".$_POST['name']."','Y')";

I tried to troubleshoot this and I used echo to display the query and
I found that
the result was something like

"Insert into tablename values("'Name','Y')
I replaced that line with

$SQL = "Insert into tablename values(NULL,'".
$_POST['name']."','Y')
And that solved the problem but I believe that should be another
way to do this and I guess that something should be in PHP.ini.
Thank you again.


On Aug 21, 5:58*pm, "C. (http://symcbean.blogspot.com/)"
<[e-mail]:

On 21 Aug, 22:43, D1R <[e-mail]:

I have to migrate a web application written in PHP and I am using
PHP 5.2.6 ISAPI.

The problem I have is related to some sql queries that have the
following format:

$sql = "insert into table_name values ('','" . $value1 . "','" .
$value2 . "',')";

This fails because PHP will translate this in something like:

Insert into table_name values (", '10','15')
In other words if I have in my script something like '10' that will
translated into
the same '10' but something like '' will make the final query string
to look different
and to fail.

This script run without problems in the old server and I guess should
be something
in PHP.ini but I cannot find what is different.

You've totally failed to isolate the problem but offerd various bits
of irrelevant information - the example you've provided gives no
indication of where the data might have come from, and the fact that
you are creating an SQL query (badly) has nothing to do with the
problem. Taking a random stab in the dark, it looks like a scope
problem - your old server may have had register_globals on but your
new one doesn't (which would also indicate that your code is badly
structured).

Read this:http://www.catb.org/~esr/faqs/smart-questions.html

and this:http://www.php.net/register_globals

and this:http://en.wikipedia.org/wiki/Structured_programming

C.
C.- Hide quoted text -

- Show quoted text -

[Jerry Stuckle]

D1R wrote:

>
Thank you for your fast answer.

I use to write code in PHP but I am not an expert so I did not
provide enough information. Sorry about that.

The code was written by somebody else and it is using MySQL as DB.
I had to migrate the web server and that was the time when I start
having problems and I discover that some options did not work.
The code I was talking about is:
$SQL = "Insert into tablename values('','".$_POST['name']."','Y')";

I tried to troubleshoot this and I used echo to display the query and
I found that
the result was something like

"Insert into tablename values("'Name','Y')
I replaced that line with

$SQL = "Insert into tablename values(NULL,'".
$_POST['name']."','Y')
And that solved the problem but I believe that should be another
way to do this and I guess that something should be in PHP.ini.
Thank you again.


On Aug 21, 5:58 pm, "C. (http://symcbean.blogspot.com/)"
<[e-mail]:

>On 21 Aug, 22:43, D1R <[e-mail]:

>>I have to migrate a web application written in PHP and I am using
PHP 5.2.6 ISAPI.
The problem I have is related to some sql queries that have the
following format:
$sql = "insert into table_name values ('','" . $value1 . "','" .
$value2 . "',')";
This fails because PHP will translate this in something like:
Insert into table_name values (", '10','15')
In other words if I have in my script something like '10' that will
translated into
the same '10' but something like '' will make the final query string
to look different
and to fail.
This script run without problems in the old server and I guess should
be something
in PHP.ini but I cannot find what is different.

You've totally failed to isolate the problem but offerd various bits
of irrelevant information - the example you've provided gives no
indication of where the data might have come from, and the fact that
you are creating an SQL query (badly) has nothing to do with the
problem. Taking a random stab in the dark, it looks like a scope
problem - your old server may have had register_globals on but your
new one doesn't (which would also indicate that your code is badly
structured).

Read this:http://www.catb.org/~esr/faqs/smart-questions.html

and this:http://www.php.net/register_globals

and this:http://en.wikipedia.org/wiki/Structured_programming

C.
C.- Hide quoted text -

- Show quoted text -

No, there is nothing in the php.ini file to change sql statements. All
they are are strings to PHP.

However, if you switched to a new server, chances are your MySQL version
changed also - and that's where your problem is. Try comp.databases.mysql.

Hint: When you're talking about SQL statements, chances are your best
place to start would be the database newsgroup, not here.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[D1R]

However, if you switched to a new server, chances are your MySQL version

changed also - and that's where your problem is. *Try comp.databases.mysql.

Hint: When you're talking about SQL statements, chances are your best
place to start would be the database newsgroup, not here.

------------------------------------

Jerry,

It is not about SQL statements it is about the fact that
if you'll do something like:

$SQL = "Insert into tablename values('','".
$_POST['name']."','Y')";
echo $SQL;

You'll get:

Insert into tablename values("'Name','Y')

while what I expect to have is:

Insert into tablename values('','Name','Y')

On the other hand

$SQL = "Insert into tablename values('1','".
$_POST['name']."','Y')";
echo $SQL;

will display

Insert into tablename values('1','Name','Y')
This worked fine in the old server.
This is the reason why I believe should be a setting in PHP.ini
but I cannot figure out what is that.

Thank you.

[Jerry Stuckle]

D1R wrote:

>However, if you switched to a new server, chances are your MySQL version
changed also - and that's where your problem is. Try comp.databases.mysql.

Hint: When you're talking about SQL statements, chances are your best
place to start would be the database newsgroup, not here.

------------------------------------

Jerry,

It is not about SQL statements it is about the fact that
if you'll do something like:

$SQL = "Insert into tablename values('','".
$_POST['name']."','Y')";
echo $SQL;

You'll get:

Insert into tablename values("'Name','Y')

while what I expect to have is:

Insert into tablename values('','Name','Y')

On the other hand

$SQL = "Insert into tablename values('1','".
$_POST['name']."','Y')";
echo $SQL;

will display

Insert into tablename values('1','Name','Y')
This worked fine in the old server.
This is the reason why I believe should be a setting in PHP.ini
but I cannot figure out what is that.

Thank you.

PHP does not substitute " for ''. One of three things has occurred:

1. You put " in there
2, You read the '' incorrectly.
3. You are not executing the code you think you are executing.

There is nothing special about SQL - it's just a string to PHP. And if
what you said actually occurred, millions of servers all over the world
would be crashing.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[JC]

"D1R" <dl****@gmail.comwrote...
: It is not about SQL statements it is about the fact that if you'll
: do something like:
:
: $SQL = "Insert into tablename values('','". $_POST['name']."','Y')";
: echo $SQL;
:
: You'll get:
:
: Insert into tablename values("'Name','Y')
:
: while what I expect to have is:
:
: Insert into tablename values('','Name','Y')

Either you are broken, your fingers are broken or your logic is
mistaken. :-)

First, I would not use variable names that could possibly be reserved
words. Yeah, I know that the dollar-symbol means it's a variable. The
point is to NOT ever define things like $SQL. But that's just me and a
whole lot of others that feel that way.

Your logic above fails to work though. You stated that PHP writes out:

: Insert into tablename values("'Name','Y')

However, note, you probably mistyped that. The terminating " fails to
appear.

Don't know if this will work, but you might also try prefixing the '
with a backslash. Check the extensions that affect string variables
(and/or security if such exist).

Also, take NOTE, that your string above, which uses the $_POST variable
is subject to SQL injection.

Try configuring the php.ini file with E_ALL to report all errors/notices.

Hope some of this helps.

--
JC
You Have More Than Five Senses
http://www.associatedcontent.com/art...ve_senses.html

[Marco Lussi]

First of all, why not declaring the table fields you want to fill in?

$sql="INSERT INTO tablename('name','value1','value2') VALUES
('$name','$value','$value2')";

So you won't need to insert Empty values (wich makes no sence to me, if
you declared a standard value in the Database).
Second thing:

D1R wrote:

>
$SQL = "Insert into tablename values('','".
$_POST['name']."','Y')";
echo $SQL;

You'll get:

Insert into tablename values("'Name','Y')

while what I expect to have is:

Insert into tablename values('','Name','Y')

*NEVER, NEVER* directly use the data sent by the user. This Data *has* to
be seen as Evil. This because of SQL Injections¹.
Write a function to avoid people altering or removing your Database
Contents. If you are not able to, I can help you.


¹: http://en.wikipedia.org/wiki/SQL_injection

[Curtis]

D1R wrote:

>However, if you switched to a new server, chances are your MySQL version
changed also - and that's where your problem is. Try comp.databases.mysql.

Hint: When you're talking about SQL statements, chances are your best
place to start would be the database newsgroup, not here.

------------------------------------

Jerry,

It is not about SQL statements it is about the fact that
if you'll do something like:

$SQL = "Insert into tablename values('','".
$_POST['name']."','Y')";
echo $SQL;

You'll get:

Insert into tablename values("'Name','Y')

while what I expect to have is:

Insert into tablename values('','Name','Y')

On the other hand

$SQL = "Insert into tablename values('1','".
$_POST['name']."','Y')";
echo $SQL;

will display

Insert into tablename values('1','Name','Y')
This worked fine in the old server.
This is the reason why I believe should be a setting in PHP.ini
but I cannot figure out what is that.

Thank you.

You don't appear to be escaping the data in $_POST['name']. You can
use mysql_real_escape_string or prepared statements if you're using
PDO or mysqli. Unless you escape the data, user data can easily be
crafted to either break the query, or inject unintended code (SQL
injection), which may be the cause of your problem.

Also, as Marco Lussi mentioned, specifying the field names is a lot
easier, as well as clearer. Check comp.databases.mysql for specifics
on the SQL itself.

--
Curtis (http://dyersweb.com)