Re: Data security - help required

[Mike van Hoof]

Well,

you can save all data encoded in the database:
http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_encode

- Mike

Chris schreef:

Ratheesh K J wrote:

Hello all,

I have a requirement of maintaining some secret information in the 
database. And this information should not be visible/accessible to 
any other person but the owner of the data.
Whilst I know that encryption/decryption is the solution for this, 
are there any other level of security that I can provide to this?


Which is the best security technique used in MySQL to store seceret 
information.


PS: Even the database admin should not be able to access anybody 
else's information


Then you're stuffed - *someone* has to be able to see everything so 
you can do a mysqldump.


*Someone* has to be able to see everything so you can grant 
permissions to the other users too :)





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ORDER BY question

[Mike van Hoof]

Hello everybody,

I got a small problem with ordering on en ENUM field. The values in this 
field are:

- to be started
- started
- finished
- canceled

And i want to order on this field, but in the direction the are above 
here (and not alpabetically).

Is that possible?

- Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY question

[Mike van Hoof]

Thanks, that is also a solution.

Friend of mine pointed me to the following:

   SELECT *, DATE_FORMAT(deadline, '%d-%m-%Y') as deadline_f,
   CASE `status`
   WHEN 'not yet started' then 1
   WHEN 'in progress' then 4
   WHEN 'finished' then 5
   WHEN 'now hiring' then 3
   WHEN 'waiting' then 2
   WHEN 'closed' then 6
   END AS sorted_grade
   FROM v_issue_project_task
   ORDER BY sorted_grade

- Mike

Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat



Christophe Gregoir schreef:

Hey Mike,

Sounds like you would be better of with an ENUM of integers, e.g. 
ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so 
on.

To answer your question:
ORDER BY `status` = 'to be started', `status` = 'started', `status` = 
'finished', `status` = 'canceled'


Mike van Hoof wrote:

Hello everybody,

I got a small problem with ordering on en ENUM field. The values in 
this field are:

- to be started
- started
- finished
- canceled

And i want to order on this field, but in the direction the are above 
here (and not alpabetically).

Is that possible?

- Mike






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Slow query

[Mike van Hoof]

Hello,

i have the following query:

   SELECT DISTINCT (
   Waarde
   ) AS bestemming
   FROM xml_kenmerk
   WHERE Omschrijving = 'Bestemming'
   AND IF (
   DatumBegin IS NOT NULL

   AND DatumBegin != '-00-00', DatumBegin = CURDATE( ) , 1 =1
   )
   AND IF (
   DatumEind IS NOT NULL

   AND DatumEind != '-00-00', DatumEind = CURDATE( ) , 1 =1
   )
   ORDER BY Waarde


Table structure:

   CREATE TABLE `xml_kenmerk` (
 `KenmerkRolid` int(11) NOT NULL auto_increment,
 `Omschrijving` varchar(255) default NULL,
 `Waarde` varchar(255) default NULL,
 `DatumBegin` date default NULL,
 `DatumEind` date default NULL,
 `OrganisatieRolid` int(11) unsigned default NULL,
 `RelatieRolid` int(11) unsigned default NULL,
 PRIMARY KEY  (`KenmerkRolid`),
 KEY `OrganisatieRolid` (`OrganisatieRolid`),
 KEY `RelatieRolid` (`RelatieRolid`),
 KEY `Omschrijving` (`Omschrijving`),
 KEY `Waarde` (`Waarde`),
 KEY `DatumBegin` (`DatumBegin`),
 KEY `DatumEind` (`DatumEind`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2163460 ;


and it contains approx. 7500 rows. But the query takes 1.5 seconds. Does 
anybody know how i can get bether preformance from this query?


- Mike

Re: Slow query

[Mike van Hoof]

Hello, I pinned down the problem to the order by line. If i leave this 
away the query is done in 0.05 seconds.


- Mike

Mike van Hoof schreef:

Hello,

i have the following query:

   SELECT DISTINCT (
   Waarde
   ) AS bestemming
   FROM xml_kenmerk
   WHERE Omschrijving = 'Bestemming'
   AND IF (
   DatumBegin IS NOT NULL

   AND DatumBegin != '-00-00', DatumBegin = CURDATE( ) , 1 =1
   )
   AND IF (
   DatumEind IS NOT NULL

   AND DatumEind != '-00-00', DatumEind = CURDATE( ) , 1 =1
   )
   ORDER BY Waarde


Table structure:

   CREATE TABLE `xml_kenmerk` (
 `KenmerkRolid` int(11) NOT NULL auto_increment,
 `Omschrijving` varchar(255) default NULL,
 `Waarde` varchar(255) default NULL,
 `DatumBegin` date default NULL,
 `DatumEind` date default NULL,
 `OrganisatieRolid` int(11) unsigned default NULL,
 `RelatieRolid` int(11) unsigned default NULL,
 PRIMARY KEY  (`KenmerkRolid`),
 KEY `OrganisatieRolid` (`OrganisatieRolid`),
 KEY `RelatieRolid` (`RelatieRolid`),
 KEY `Omschrijving` (`Omschrijving`),
 KEY `Waarde` (`Waarde`),
 KEY `DatumBegin` (`DatumBegin`),
 KEY `DatumEind` (`DatumEind`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2163460 ;


and it contains approx. 7500 rows. But the query takes 1.5 seconds. 
Does anybody know how i can get bether preformance from this query?


- Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MYSQL REGEXP help

[Mike van Hoof]

Hello,

i am try to make a regular expression work, but keep getting this error 
message:


does anyone know how i can make it work?
The query is:

SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])';

So it has to match each starting 'b' and all the b's pf following words. 
But now followed by a v(line end) or a v followed by a space.


so it should match:

'b test'
'test b'
'test b bv'
'bv b test'

and NOT

'test bv'
'bv test'

Any idea's?!

Thanks, mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MYSQL REGEXP help

[Mike van Hoof]

Hello,

i am try to make a regular expression work, but keep getting this error 
message:


does anyone know how i can make it work?
The query is:

SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])';

So it has to match each starting 'b' and all the b's pf following words. 
But now followed by a v(line end) or a v followed by a space.


so it should match:

'b test'
'test b'
'test b bv'
'bv b test'

and NOT

'test bv'
'bv test'

Any idea's?!

Thanks, mike

Re: MYSQL REGEXP help

[Mike van Hoof]

Hello,

this doesn't work:

mysql SELECT 'oer bv' REGEXP '[b|^b](!?[v$|v])';
++
| 'oer bv' REGEXP '[b|^b](!?[v$|v])' |
++
|  1 |
++
1 row in set (0.00 sec)

He shouldn't select this one, because it says 'bv' and no other b

Mike

ViSolve DB Team schreef:

Hi,

[ERROR 1139 (42000): Got error 'repetition-operator operand invalid' 
from regexp]

because,
In your query,
'!' is an Operator and ? is a wild character.  Only wildcharacters 
should be follow the Operators.

Try with.

SELECT 'boer bv' REGEXP '[b|^b](!?[v$|v])';


Thanks
ViSolve DB Team

- Original Message - From: Mike van Hoof [EMAIL PROTECTED]
To: mysql mysql@lists.mysql.com
Sent: Monday, January 08, 2007 1:36 PM
Subject: MYSQL REGEXP help



Hello,

i am try to make a regular expression work, but keep getting this 
error message:


does anyone know how i can make it work?
The query is:

SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])';

So it has to match each starting 'b' and all the b's pf following 
words. But now followed by a v(line end) or a v followed by a space.


so it should match:

'b test'
'test b'
'test b bv'
'bv b test'

and NOT

'test bv'
'bv test'

Any idea's?!

Thanks, mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: find in list

[Mike van Hoof]

Charlie Schaubmair schreef:
 Hello, 

I want to do a query where I only gt the results by an numeric value: 

select * from MyTable where 1 IN someFieldInMyTable 
I know this query doesn't work, but maybe anyone knows what I mean. 

1 can be a value betwenn 1 and 23 
someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21 

br 
Charlie


  

Hey

try:

SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
This will also give you results like 21

What I usally do in these cases is build the values like this:

[1][2][3][21]

And then te query:

SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'

Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: AW: find in list

[Mike van Hoof]

Hey Charlie,

maybe there is, but i don't know why... because when you do a like query 
it also finds the 21 when you do a like on the 1.

what you maybe can do (not tested) is:

SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable WHERE 
wherefield='%,1,%'

Now your someFieldInMyTable is CONCATed to ,1,2,3,4,5,6,9,21, and over that 
value the where clause is done...

Maybe it helps

Mike 




Charlie Schaubmair schreef:

Hello Mike,

thx, but isn't there another way?

br
Charlie 

  

-Ursprüngliche Nachricht-
Von: Mike van Hoof [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 16. August 2006 10:46

An: Charlie Schaubmair
Cc: mysql@lists.mysql.com
Betreff: Re: find in list

Charlie Schaubmair schreef:


 Hello,

I want to do a query where I only gt the results by an 
  
numeric value: 

select * from MyTable where 1 IN someFieldInMyTable I know 
  
this query 


doesn't work, but maybe anyone knows what I mean.

1 can be a value betwenn 1 and 23
someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21

br
Charlie

  
  

Hey

try:

SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
This will also give you results like 21

What I usally do in these cases is build the values like this:

[1][2][3][21]

And then te query:

SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'

Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
 
Uw bedrijf voor Multimedia op Maat







  



--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat

Re: AW: find in list

[Mike van Hoof]

Sorry, query was wrong... has to be:

SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable 
WHERE wherefield LIKE '%,1,%'


Mike van Hoof schreef:

Hey Charlie,

maybe there is, but i don't know why... because when you do a like 
query it also finds the 21 when you do a like on the 1.

what you maybe can do (not tested) is:

SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM 
MyTable WHERE wherefield='%,1,%'


Now your someFieldInMyTable is CONCATed to ,1,2,3,4,5,6,9,21, and over 
that value the where clause is done...


Maybe it helps

Mike


Charlie Schaubmair schreef:

Hello Mike,

thx, but isn't there another way?

br
Charlie
 

-Ursprüngliche Nachricht-
Von: Mike van Hoof [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 16. 
August 2006 10:46

An: Charlie Schaubmair
Cc: mysql@lists.mysql.com
Betreff: Re: find in list

Charlie Schaubmair schreef:
   

 Hello,

I want to do a query where I only gt the results by an   
numeric value:
select * from MyTable where 1 IN someFieldInMyTable I know   
this query

doesn't work, but maybe anyone knows what I mean.

1 can be a value betwenn 1 and 23
someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21

br
Charlie



Hey

try:

SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
This will also give you results like 21

What I usally do in these cases is build the values like this:

[1][2][3][21]

And then te query:

SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'

Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
 
Uw bedrijf voor Multimedia op Maat







  






--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: returning username/pass from 2 tables

[Mike van Hoof]

Renato Golin schreef:

[EMAIL PROTECTED] wrote:
How do I check two tables is it? Username and userpass are submitted 
through a from and are unique


$sql = SELECT username, userpass FROM mytable, mytable2 WHERE 
username = '$username' AND userpass = '$userpass';


This way you'll have an ambiguous error as username and userpass 
belongs to both table (as far as I could understand). You can do both 
selects or do something like this:


select a.user, a.pass, b.user, b.pass
from table1 a, table2 b
where (...)

Or create a MERGE storage engine if both your tables are identical:

http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html

cheers,
--renato


Or if your tables are not identical you can use a LEFT JOIN:
http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Converting TEXT to BLOB with special chars

[Mike van Hoof]

Hello,

I am having a problem, and am hoping this is the wright list to post to.

I have a TEXT field with text in them (duh!)... this text also contains 
special characters like é ß ü etc...
But when i convert the TEXT field to a BLOB field these signs are lost 
and i get �


So does anyone know a workaround for this (and not converting to a BLOB 
is not an option... sorry)


Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Converting TEXT to BLOB with special chars

[Mike van Hoof]

Addison, Mark schreef:

From: Mike van Hoof  Sent: 18 July 2006 12:18
  

To: Addison, Mark
Subject: Re: Converting TEXT to BLOB with special chars

Addison, Mark schreef: 


From: Mike van Hoof  Sent: 18 July 2006 10:49
	  


Hello,

		I am having a problem, and am hoping this is 
the wright list 
		to post to.


		I have a TEXT field with text in them (duh!)... 
this text 
		also contains 
		special characters like é ß ü etc...
		But when i convert the TEXT field to a BLOB 
field these signs 
		are lost 
		and i get  
		
		So does anyone know a workaround for this (and 
not converting 
		to a BLOB 
		is not an option... sorry


What version of mysql are you using?

Version 4.1.12 


What character set is the TEXT column?

latin1_swedish_ci

What are you using to display this text?

PHP / HTML

Although my guess is that the characters are not lost, it's just
that the software is now just getting binary (blob) data back
and doesn't know how to decode it.
	If you convert the field back to TEXT can you see the 
chars again?  


Yes i do...



Looks like the PHP will need to explicitly decode the data from mysql
as latin1_swedish_ci before display. I don't know PHP but maybe 
someone else round here knows how...


Are you sure you want to store text in a blob, seems to be
making life hard for yourself?

mark
--
  

Well.. gonna try some text-converting in php then...

And yeah, it really needs to be a blob field... not my choice, but we 
got a CMS which operates on field types from MySQL... and a blob field 
wil generate an WYSIWYG field... but i think i am going to have a chat 
with my boss...


Thanks for the help, and i will post a solution i find back on here

Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat

Re: Converting TEXT to BLOB with special chars

[Mike van Hoof]

Chris Sansom schreef:

At 13:40 +0200 18/7/06, Mike van Hoof wrote:

Well.. gonna try some text-converting in php then...

And yeah, it really needs to be a blob field... not my choice, but we 
got a CMS which operates on field types from MySQL... and a blob 
field wil generate an WYSIWYG field... but i think i am going to have 
a chat with my boss...


You could, it seems to me, get round all this by doing everything, 
from soup to nuts, in utf-8. Is there a reason why it can't all be in 
utf-8?


Yeah, this i indeed something i can do but did it in another way 
now... I just made it an LONGTEXT field, and did a small edit in the CMS 
system, so it works now.
And for the everything in utf8... will try this next time i get this 
kind of a problem... the website needs to be online this afternoon


Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]