SQL Query question - errors with ;

[TeddyBare69]

I have created the hack viPortfolio currently in Beta testing. I have gotten most of the errors taken care of but there is one that keeps coming back. I still have yet to be able to recreate it.

On some mysql databases, they get an error if there is a ; in the sql string during a doqueries call. Does anybody know why that is? My test/development server handles it just fine but most other servers including my production server does not. My system versions are below, I am not sure what versions other people are on exactly but it seems to be a wide variaty.

Anybody else had this problem?

mysql-max-4.0.1
php version 4.1.2
vBulletin 2.2.6

Thanks for your help!

[Dark_Wizard]

Can you paste the code for us to review?

[TeddyBare69]

You actually already have the code. It is doquery(). Here is the section of code that the error occured:

$loc = strpos( $val, ';' );
if ( $loc > 0 && strlen(trim($val)) > 1 )
{
$val[strlen($val) - 1] = ' ';
str_replace( ';', ' ', $val );
$DB_site->query($val);
if ($onvservers==1 and substr($val, 0, 5)=="ALTER")
{
$DB_site->link_id=0;
@mysql_close();

sleep(1);
$DB_site->connect();
}
}
I get the error on the $DB_site->query($val); line.

I have a function that takes a file name and reads the contents of the file. The contents of the file is an SQL statement which can be on either one line or multiple lines but has to end with a ; . The last time I got the error it was executing the following SQL

CREATE TABLE IF NOT EXISTS `viPortfolio` (
`viPortfolioid` smallint(6) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL default '0',
`mimeid` mediumint unsigned NOT NULL default '1',
`filename` VARCHAR(250) NOT NULL,
`file` BLOB NOT NULL,
`thumb` BLOB NOT NULL,
`width` int(10) unsigned NOT NULL default '0',
`height` int(10) unsigned NOT NULL default '0',
`text` mediumtext NOT NULL,
`startdate` date NOT NULL,
`enddate` date,
`updateid` MEDIUMINT,
`viewcount` MEDIUMINT default '0',
PRIMARY KEY `userid` (`viPortfolioid`, `userid`, `mimeid`)
) TYPE=MyISAM;

Iinitialy I had place this line before the query statement:
$val[strlen($val) - 1] = ' ';

I must have had a space at the end of the line or something because the ; got past it. Then I replaced that line with the line below.
str_replace( ';', ' ', $val );

Yes, this does fix the problem. It takes the ; out of the statement so that MySql doesn't have to deal with it. I still would like to know why it cares. A ; has always been a statement ender in any DB system I have used before?

[TeddyBare69]

Dark_Wizard,
Did you post a question then delete it? I got an email but I can't find the post here?

[Dark_Wizard]

I did...and now I'm puzzled...wtf? I didn't delete it....

[TeddyBare69]

I deleted the email but I think your question was:

what are these ` doing in the sql file?

My highly educated, extremely knowledgable answer to that question is "because"? I seen it done before and it looked more organized than not doing it so I started to use.

Now that I think about it, it would make sense that could cause errors, it doesn't make sense that it causes errors on other servers but not mine? Unless there was some feature was added to handle those apostrophies?

[Dark_Wizard]

Try removing them as I did and the install worked fine for me...I use PHP 4.2.2 on my server...

[TeddyBare69]

I just did that and I am going to send the update sql files to Apok2002 and see if it fixes his problem also. I have Mysql 4.1.2 and I dont' have a problem with them.

You have 4.2.2 and you did have a problem with the sql files. Now that you removed the ' and ` it fixes the problem. I sure hope so. I will walk away with a lesson well learned. :)

[Dark_Wizard]

I have PHP 4.2.2 not MySQL 4.2.2. My MySQL is version 3.23.39

[TeddyBare69]

yeah, my mistake. This was the very first bug reported and looks like it might be the very last one to be fixed. A little flustered is all. I have php 4.1.2 and mysql 4.0.1-alpha

I can defanately see this as a minor code improvement that probably didn't even get notied in the change log or final release. Thanks agian for your help. What kind of virtual brew you drink? Have one on me :D

[Dark_Wizard]

I take it is working for the others now? Absolut and 7 :p