I am looking for a way to pass a string threw a query to compare it to a rows column and return true or false

CheapWebHosting.co.nz

If I have a file with multi-byte characters how can I ensure that the data gets imported properly with a LOAD

should work just fine, the biggest bottleneck is going to be the replication.
and that shouldnt really be that big of an issue

yeah
multi master is a good solution here?

worst thing that can happen is when something is changed on siteA it could be a minute before it's seen on siteB
multi-master is about the only solution, you are going to be writing to all your php mysql web hosting hosts

Burnettj_hm, which version of mysql are you using?

5.0 w/replication migration right now

I have to leave now. I will be back in 2 hours if you are still around

5.0.32
debian stable
(etch)

he's gone

sure nuff
damn, gotta burn 6 more minutes before I can start the migration

i wish i could figure out the Bandwidth usage of replication

tcpdump
ntop might help

graph it with cacti/zenoss/zabbix/etc

I am looking for a way to pass a string threw a query to compare it to a row's column and return true or false without allowing the user to actually retrive the columns value. Anyone have any clue?

how would you distinguish between replication traffic, normal mysql traffic, and everything else?

"SELECT COMPARE('password', pass) AS match_result FROM user WHERE uid=1;"

hmm good point

/msg nickserv set unfiltered on
oops
damn spaces

Here is a better idea of what I am looking for... I am looking for a way to make a column in a table comparable but not readable by certain users.

not much info on slave_compressed_protocol out there
679 hits total on google

you'll have to filter on source-destination ip
only look at traffic between master-slave

you can do a query like "select column = 'blah' from table" and it will return a 1 or 0 depending on if it matches or not

but for me to do that i would have to grant the user select permissions on that column making that column readable
i dont want this user retrieving whats in the column, just want them to compare it to a string

err

maybe I am not understanding, they are going to have to retrieve it in order to compare it to anything

maybe some kind of stored procedure?

i have looked into writing functions
havnt looked at procedures

even with a sproc, they user will have to be granted access to the data being retrieved won't they?

but am i going to be able to grant a user the procedure? such as GRANT SELECT(id), COMPARE ON dbase.tbl TO usr@host
with the functions that is the way i figured it is that the user would still need select privileges for that column
i could possibly write some type of native function for mysql
but only want to do that as a last resort

I haven't played with procedures alot, but it's my understanding that the user will still have to have select on dbase.tbl in order to compare it

yeah... maybe. This is kind of a weird design. Might have to move this into an application and provide the user with an java api hosting to compare it instead of giving direct access to the DB

and there is nothing "that you guys know of" that can do this already?
the point was to make it unreachable

not that I know of

so if someone located an sql injection exploit in a php script he/she would not be able to retrive / delete / update passwords
and same thing if someone got a shell or something up on the server, they wouldnt be able to retrive the information

well... you aren't storing them in plain text, are you?

no
they are encrypted
but even then you would be relying on an encryption
that can be easily brute forced

we have this same type of setup and the security is handled mostly in the app, the data is all encrypted and the user has to pass a pgp key in order to decrypt the key that was used to encrypt the data

but you are relying on the application

yep

if someone gets a shell up on the application they can easily display this information

can't be easily brute forced if you force users to select complex enough passwords

but that is stupid to do

even with a shell, without the users private pgp key they can't decrypt the key that the data was encrypted with

why do that when you can store this information securely?
but they have access to alter this script or could work at altering the script
or find a way to get this private key and use it
my idea would mean that no matter what they had they would need root access or a privileged user to get the information

then I have bigger issues, cause my admins better be able to keep their keys secure, if not they will be unemployed soon thereafter.

and i would rather rely on root + encryption + only compare the nust an encryption
not just*
just*
lol

with your method, if they get root, all they have to do is grant more privs

alright well, seems like you guys dont belive there is any way of doing it currently
i will keep searching just to be sure and if not i will write my own way to do it within mysql
and i will let you guys know of the results

good luck

but with your idea all they have to do is get root to

yep

if i have to i will backup the user table and remove the root user and replace the user table when i need to use root
lol

then they'll just restart mysql with no grant tables and add their own user
it's a never ending cycle

good luck getting out of the jail
i can deny local host root
and only allow it from my dev machine
but eaither way i think my idea would be more secure then just replying on an encryption
with you that key needs to be sent to the server for your application to function correctly

yep

anything imcomming can easily be captured and stored

nope, it's all over ssh

alright then maybe but
i am talking about a web application

so use ssl

but even then
if someone gets enough access to write to this application
i am screwed

you can "but even then" any security measures, all you can do is take the most steps possible while maintaining useability and keep your servers as secure as possible

i would rather just not have this data accessible by anyone other then root

even that is exploitable

yes
but not as exploitable as not having it

true

thats what i am saying you have nothing to fall back on
idk i just think that adding this extra layer of security might not make it really secure but it sure would help making things alot more difficult

what do you mean? I have restricted network, data that is behind 2 levels of encryption, secured traffic to and from server only and data split into separate groups encrypted with different keys that are only accessible to certain users.

but even then
i could have the same setup with this extra layer and mine would be just that much more secure
i aint saying that your setup is bad or anything but i would rather have as much as i can get without effecting the performance
i would rather have data checked by the server rather then checked by a php application
checked by the server it is stored on

that would be better, but not very feasable for me.
I don't have to time or desire to write that much code

that much code?

and I'm still not sure it's possible without actually patching mysql
I am not sure the underlying security will allow you to compare without select

well that was the idea, if all else failed i was going to extend mysql

that's going to be a bitch

but really didnt want to do that until i was positive there is nothing else like it

unless you are already familiar with the mysql codebase

3 learning new things

I'm pretty sure the additional bit of security isn't worth the work you're putting into it unless you are storing some extremely high value data

the more i know the more i can do
but it wont be just for that extra bit of security
in the proccess i will learn so much more then i already know

well maybe if you want to become a mysql developer... otherwise I would rather spend my time learning other things

I've looked at the code and unless your tracking down a bug, your not going to learn how to use mysql by patching the code

you dont know who i am or what i know and that can make the difference

what is the best way to migrate dataabses from one remote mysql server to another?

everyone is different, i can look at a pencil and tell you how man feet it is from your current location to the sun

mysqldump

but you might look at it as impossible

ToeBee, so mysqldump to a file , and then to the other db?
or is it possible to pipe it directly to the other db?

in theory you could pipe the output of mysqldump directly over the network and to the other db... but I would usually prefer to have a file so that if the migration breaks halfway through you have a backup file and don't have to do a clean dump

ToeBee, okay thanks, just wanted a more experienced opinion
will do
ToeBee, i'm reading the man, but is it fair to say that i can't do --databases prefix_* ?

you want to dump multiple (all?) databases?
there is the --all-databases argument too

ToeBee, saw that, but i'm dumping from a hos
t
so i can't get all, just some
adn they have a prefix in common

the man page is somewhat ambiguous on what delimeter should be used for the --databases=
spaces i take it

hmm yeah not sure. try spaces and see what happens

mysql.org has it with spaces, so i'll tryt that

are all indexes on a table innactive while other indexes are being created for cols in that table?

Burnettj_hm, still around?

i ask because a query that was pretty fast yesterday is really slow today.. only diff i can think of is new index being created on a col

yeah
trying to fix monitoring for this new box
what's up?

hi, if you have one php page with a connection to mysql and then open a second connection to same database, does it not work?

it works

But of course it does

though there is a parameter which forces _new_ connection to be open
instead of old to be reused.
bool $new_link

hmm, i could not get the second connection to fetch the array
I typed it over like 30 times
I mean fetch the rows into assoc_array

ok, I'm going nuts trying to setup a cluster, I can't get it working
what's the difference between /var/lib/mysql and /var/lib/mysql-cluster as your datadir?

probably just distro-specific configuration

maybe
oh
well
is mysql-cluster specific to the clustering? or is it just like /var/lib/mysql where all the databases are kept?

mysql cluster (aka NDB) is distributed redundant storage system
which is good, because it is distributed and redundant

yep

and it is bad, because it is distributed and redundant

id=2 @192.168.0.41 (Version: 5.0.32, starting, Nodegroup: 0)
just stays like that forever
no firewall either

moin

moin moin

looks like it doesn't like resolving host names from /etc/hosts

hello, i've a 40mb .csv, i would to import it ina localhost db, but i've still 'the file is over 16mb max_filesize', why after i checked php.ini, it no changes the upload_max_filesize and stuff?
please

how do you import it?

phpmyadmin and a script

you have to restart apache when you change the php.ini

both should works

or -HUP it

sure
tried via shell too

try from command line mysql

already tried

why bother with 3rd party stuff?
and that should work
and?

still 'file too big'
and phpinfo says 'max_upload_filesize = 32mb' when i set it as 80mb
uhmfff dunno if all works nice here

i said "mysql command line client". rings a bell?

but i would to try to do one db, and can't try online, maybe
If you have shell (command line) access, use MySQL to import the files directly. You can do this by issuing the "source" command from within MySQL: source filename.sql.
The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize.
that's all i've done

*sigh*

mysql command line client==source filename.sql isn't it?
no, eh?
Allowed memory size of 33554432 bytes exhausted (tried to allocate 35402828 bytes) in C

Hello. Is it still possible to download a free MySQL version? I could only find the enterprise edtition on the webpage

http://dev.mysql.com/downloads/

TheGoldDIggah, it is, mysql.com

tibykei am there....but i only see the enterprise edition to download
or I mean, buy

look harder

Is it the Maxdb?

or mind obert's url
bw is unde enterprise section

ahh its the community server

on the right hand side of the screen, "MySQL community server download"

'lo

indeed
lo

alter table bookrecs drop foreign key submitter;
ERROR 1025 (HY000): Error on rename of './Tracy/bookrecs' to './Tracy/#sql2-db7-22cad' (errno: 152)

ack...have to log in first

I'm starting a video-site. On each video page I want to assosiate the following with a ID: Header, Player Code and Comments. Any recommended setup? I use phpmyadmin.

recommende setup?
be more specific.

so what? i dont get it
phpmyadmin is a web-based frorntend to mysql server

fredl This is the first time EVER I create db tables

okay, so comments is different for every video, do you just want one comment or more?

fredl What's best for just veiwing data + one field that can be added by users

GRANT EXECUTE, SELECT(uid) ON wcdb.pass TO 'wc'@'%';

Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

fredl different comment for every video

someone mind helping me out?

fredl And useres should be able to post

okay, and header is different for every video too I presume?
and what is 'Player Code' ?

SkareCrow, mysql.com/grant, read it

SkareCrow, you need to take out (uid) I think.

fredl a javascript

fredl, he needs to rtfm

true
Gabbsmo, it's basically pretty simple. you have to identify first what is unique to a video and what can be used for multiple videos.
Gabbsmo, so you have 1 - 1 relations and 1 - n relations

the execute is the issue unless execute doesnt like just have exe privs on uid

fredl ok...

Gabbsmo, so say 'Player Code' is an object that will be used for multiple videos....

fredl I guess all of it is unique

Gabbsmo, then you want to create a table 'Player Code' with the specifics for that player code in it.
Gabbsmo, then in your videoz table you can link to that player code preferably using something like a foreign key.
you know how that works?

fredl no idea

Gabbsmo, in that case I'd recommend first getting yourself a book.
it's more than I can explain in a few lines on IRC.
I'll give you a little clue though.

fredl I have phpmyadmin can I do this with it?

create table playercodes (id int not null auto_increment, playercode TEXT, primary key(id)) type="innodb"
I don't care about phpmyadmin.

So why a primary key?

huh...

And what does noodb mean?
innodm^^

ok you need a book pal.

^__^

badly
so anybody know why I can't drop my foreign key?

fredl well, no the table is there
How do I write in it?

Gabbsmo, FGI
READ

*sigh*

I'm more than willing to explain some things but if you don't know how to insert data in a table you're WAY OFF

explain problem again

kimseong, alter table bookrecs drop foreign key submitter;
Error on rename of './Tracy/bookrecs' to './Tracy/#sql2-db7-22cad' (errno: 152)

drop constraint probably
!perror 152

Cannot delete a parent row

can provide the line on foreign key in show create tabe bookrecs ?

hmm RTFM for me too eh?

hehe

foreign key(submitter) references profiles(id),

you need the constraint name, not the column name

'k, tnx
'tis just that the manual is getting so huge

need to learn to search for the correct page fast

How do i check if port 3306 is open on my machine?

so when I do show create table bookrecs....
I get CONSTRAINT `bookrecs_ibfk_1` FOREIGN KEY (`submitter`) REFERENCES `profiles` (`id`)

the constraint name

so I should do alter table bookrecs drop foreign key bookrecs_ibfk_1 ?

try that
telnet hsotname 3306

voila that works. neato.

from wherever you need to connect to your port 3306
common mistake to use the column name, for drop index too

TheGoldDIggah, man netstat, man fuser, man telnet, man man

Hi
I have some string in some programing language (ie. perl) and i want to use this string in mysql regexp. How to escape all special chars in the string? Is there any kind way?

woot stored procedures work for what i was wanting to do
i have a test page up if anyone wants to play
http://www.warpedcoders.org/exploit_me.php --- source link at top of page
objective is to get a password

Why do you need stored procedure for single SELECT??

the reason is so taht the user cannot retrive the users hash
only compare a hash to a hash in the table

what is the command for getting a databae overview of all databases in the server

show databases

You better do it as a function by the way not procedure

why is that?

And don't use PASSWORD() function. It is for internal use for MySQL users only. Use some other hash functions instead e.g. MD5()

this was just a test
i know that
these are just test tables and a test procedure for an idea
probally going to use SHA-512 or SHA1 or something simler
md5-sha-512

It's much better to use function wich returns TRUE,FALSE instead of procedure

alright i will look into it, thanks for the sugesstion

GRANT USAGE ON *.* TO 'wc'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
Not very secure to expose that that

how did you get it

http://www.warpedcoders.org/exploit_me.php

that is the whole point of this
the password is just password, but how did you retrive it?

Easy. SELECT CURRENT_USER(); SHOW GRANTS FOR ...

thanks

Good you didn't give FILE_Priv to that user

doesn't even need that
'show grants'
http://www.warpedcoders.org/exploit_me.php?query=show+grants%3B&sQuery=Execute

Yjay
Hi

hey salle, having a good weekend?

salle already exposed that but thanks

That's what I did. If there was FILE you can get much more interesting data

there is nothing on this box

hah

perfect time to do this :P

your password is now foobar too :P

thanks leith
lol
now how to block it

Block what? SQL injection?

'Leith from freenode #mysql has successfully retrived and changed the wrong password but thanks will be back in a second!.'

block showing user grants and users changing there password

Users changing their own password you can't prevent

i will figure something out
lol

Labas Domai

don't allow set or show in the input box

hi!

do a regexp for 'set password'

What about SELECT .. FROM INFORMATION_SCHEMA. ?

well... that will not show passwords etc.

make mysql.user read only
And SET PASSWORD won't work

true

can't grant any global privs then either
hey Kai

Morning Mark

did you just do it again?

nope

hmm

I would guess domas had a go :P

nope
was doing more serious stuff.

well, wasn't me this time, not even looking at it now :P

there goes just didnt update the grant

t connect Domas to "serious"
What is 5.0.41-Dotdeb_1.dotdeb.2 ?

mysql ver
mysql server ver

some other things to beware of
I could do them to show it, but I will just tell you
'DROP'
'TRUNCATE'

wont work

'ALTER'

shouldnt

He only grants SELECT

ahh good

One can still DoS your server with SELECT, but that's another story

yea, i have been hacking for some 5 years just not to familer with SQL and alot of its functions
i have only ever needed to know basic sql

pass changed again
heh

You only need very basic SQL for DoS
Take a look at SHOW PROCESSLIST

the password changed again?

yea
i tried to show processlist

is workin fine for me right now
and like 5 other people are playing with it atm

oh no
woops
its because i did show logs

anybody in here ever travelled the amazonas?

user table only has 4 rows right?

yes
http://www.warpedcoders.org/exploit_me.php?source=1
scroll down

Howver there's nothing which can stop me to do: select * from user, user, ....... user

thats fine lol

If I join it 10 times I get 1048576 lines result
How big it will be with 20 joins? 4^10
20