Fields being dropped 2

[audiopro]

I am having a problem with a particular table on a remote server where certain fields are bing dropped and more recently the table records were blanked. The records are still there but contain no data. I have restored a backup and all is well again.

The MySQL table is being accessed from perl and the commands are in the following format although I have changed the var names for this post. Is this format open to attack from malicious code being inserted into it?
$VAR[$x] is created within the script and not user input.
My question is

$sql="SELECT F1, F2 FROM TABLENAME WHERE F3=\"$VAR[$x]\"";
$sth=$dbh->prepare($sql);
$sth->execute();
$rows=$sth->rows;

The ISP have been doing some upgrades but they assure me that they have not changed the data.
Is every MySQL transaction usually logged somewhere?

Keith

http://www.studiosoft.co.uk

 

[DonQuichote]

I do not know the Perl database library that is being used, but if it supports more than one query at the same time, it is open to all sorts of nastiness.

Just imagine what happens if $VAR[$x] contains:
";DROP TABLE TABLENAME;--"

But it can also contain
" OR 1=1 --
and show the entire table. I once had such an issue with a site at a former employer. With this construction, the login query would result in the whole table being pushed to the client. There were further checks that rejected this outcome, but the system was brought to a slowdown nonetheless.

If your variable is not uncleaned user input, the above examples are off course only possible if you allow it to happen.

Please note that if the server ever gets changed to ANSI mode, your quotes will stop working. In standard SQL, double quotes denote identifiers, not strings.

As far as I know, logging is not on by default. I always switch on the querylog on my development systems and test systems, but this can lead to space and performance issues on live systems.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

Please note that if the server ever gets changed to ANSI mode, your quotes will stop working. In standard SQL, double quotes denote identifiers, not strings.

excellent point, but then it would create an error message, as there would be no matching column by that name


it doesn't explain how a record could still be there but be blank

in fact, a record in the table being blank doesn't make sense

r937.com | rudy.ca

 

[audiopro]

Thanks for the replies.
The only user access is a search box which on submission, has anything other than A-Z, a-z and 0-9 removed. Then it is thrown at this query.

"SELECT F1,F2,F3,F4 FROM TABLE WHERE F5='YES' AND F6<>'NO' AND (F7 LIKE '%$TERM%' OR F8 LIKE '%$TERM%' OR F9 LIKE '%$TERM%'OR F10 LIKE '%$TERM%') ORDER BY RAND()";

The search term is also logged and there are no code attempts in there. The ISP has been doing some work but to drop a few fields from a table seems more like a deliberate act. Any suggestions?


Keith

http://www.studiosoft.co.uk

 

[r937]

whether your host did actually "drop a few fields from the table" can be confirmed by running this query:

SHOW CREATE TABLE [i]tablename[/i]

i would expect that if the fields had actually been dropped, the host would not have ALTERed the table again to put them back

i would still suspect the perl code

r937.com | rudy.ca

 

[audiopro]

Thanks, I have backups of all the tables and have restored them and all is well. I have added logging of every query and ADDR to the main script to see if I can work out what is going on. I will speak to the techs at the ISP tomorrow and see if they can shed some light on the problem.

Keith

http://www.studiosoft.co.uk

 

[audiopro]

ISP suggests locking down the structure of the database. This is on a shared server, what is the best tool, they suggest PHPMyAdmin.php, is there a way to code it (similar to chmod)rather than having to learn all those bells and whistles.

Keith

http://www.studiosoft.co.uk

 

[r937]

ask your ISP what "locking down the structure" means



r937.com | rudy.ca

 

[audiopro]

I think they mean setting some sort of permissions to prevent the table structure from being altered. This is the latest response from their tech support, which I believe is in Tailand!

It is unfortunately not possible since this is a shared hosting platform. The Database is also a shared platform. We can not slow down the performance of the db server to log one domain activities. All due respect, our server does not goes out and dropping field. It will sounds more possible to drop the whole db from the server side since the client does not have this permission.

What I can recommend is to use PhpMyAdmin to make sure the db user right does not have the structure alteration permission.

We are still considering the possibility of us being hacked but I am unsure as to how this could be done without the logs catching the rogue query.

Keith

http://www.studiosoft.co.uk

 

[audiopro]

I am looking into security issues even though I am not sure if the site has been hacked. It seems strange that the same 2 tables are being affected while the other tables remain unchanged. There are no commands in any of my scripts to drop tables as I saw that as a security issue. Is it possible to make my tables read only and only toggle them back with a sysadmin command.

Keith

http://www.studiosoft.co.uk