Re: 1' and '1' or '1
Hi Dave, 1st: Never, never, never store passwords in plain text!! Just don't do it. Store a hash of the password (ie md5 or something else). 2nd: Never pass any input from the Internet directly into a query without first checking it for sql injection. Take a look at Wikipedia article for a brief explanation and several links to further info. http://en.wikipedia.org/wiki/SQL_injection /Johan Critters wrote: Hi A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar hack Appreciate any feedback. -- Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
Hi Cor, Don't know if that function exists in MySQL... If you by any chance is using PHP you can do it by using ucfirst(str) But I quote the User Comment at http://dev.mysql.com/doc/refman/5.0/en/string-functions.html from Tom O'Malley: quote Posted by Tom O'Malley on April 18 2006 1:16am An example of how to make the first letter in a string uppercase - analogous to UCFIRST SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName /quote /Johan C.R.Vegelin wrote: Hi List, I want to convert strings to proper-case, where only the 1st char of each word is uppercase, such as: This Is An Example. Any idea how to do this with MySQL 5.0.15 ? Thanks, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to solve this problem?
First I would advice you to take a closer look at: http://dev.mysql.com/doc/refman/5.0/en/join.html It will answer your question. /Johan ??? wrote: Hi There is a schema example below: (From A first course in database system) Product (maker, model,type) Pc (model, speed, ram, hd, rd, price) Laptop (model, speed, ram, hd, screen, price) Printer (model, color, type, price) The statement below seems wrong based on MySQL 5.0, though it is from the Solutions: (SELECT maker, model, type AS productType FROM Product) RIGHT NATURAL OUTER JOIN ((PC FULL NATURAL OUTER JOIN Laptop) FULL NATURAL OUTER JOIN Printer); Dose MySql support this statement? And how I can solve it? BJUT Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
My God! Rhino, that was a very long and very good answer!! Impressive!! /Johan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outfile syntax and out of memory
Hi, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! If I instead insert the following code in queryfile.sql: INTO OUTFILE 'outfile.txt' Now my outfile.txt don't get the first row with the column names, and any NULL values are exported as \N. This is a big problem, cause the import function that exist where I send the data only accept the format I get using mysql queryfile.sql outfile.txt. Any help??! Ideas?? Can I in any way format my output to print the column names and print NULL values as 'nothing'?? Regards, /Johan Lundqvist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outfile syntax and out of memory
Hi George, To do it incrementally is not really an option, since i have to run it as a script during a short time-frame every night, and theres simply not time to process the files. The outfile is about 2 - 10 Gb every time. The tables have about 100 - 180 columns, and to do a COALESCE would create humongous sql-statements. I might also have wrote it a bit ambigous in my question; I don't want the word nothing, I really want the field to contain nothing - as in ''. Regards, /Johan - Ua, Sweden George Law wrote: Johan, have you thought about doing this incrementally? ie - 25% at a time x 4 to show something for NULL, you can use the COALESCE function. ie - COALESCE(column,'nothing') -- George Law VoIP Network Developer 864-678-3161 [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 10:16 AM To: mysql@lists.mysql.com Subject: Outfile syntax and out of memory Hi, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! If I instead insert the following code in queryfile.sql: INTO OUTFILE 'outfile.txt' Now my outfile.txt don't get the first row with the column names, and any NULL values are exported as \N. This is a big problem, cause the import function that exist where I send the data only accept the format I get using mysql queryfile.sql outfile.txt. Any help??! Ideas?? Can I in any way format my output to print the column names and print NULL values as 'nothing'?? Regards, /Johan Lundqvist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outfile syntax and out of memory
Hi, Where should I increase max_allowed packet?? I get a error from Windows (yes, I know... it's running on a M$-os, not my bad - not my desicion). The results is about 2 - 10 Gb of data. Regards, /Johan Dilipkumar wrote: Hi, Increase max_allowed packet to 1.5 gb and then try to import your data . eg ; In mysql prompt run the file as *use database \. /tmp/filename.txt * Johan Lundqvist wrote: Hi, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! If I instead insert the following code in queryfile.sql: INTO OUTFILE 'outfile.txt' Now my outfile.txt don't get the first row with the column names, and any NULL values are exported as \N. This is a big problem, cause the import function that exist where I send the data only accept the format I get using mysql queryfile.sql outfile.txt. Any help??! Ideas?? Can I in any way format my output to print the column names and print NULL values as 'nothing'?? Regards, /Johan Lundqvist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot restart service MySQL
In Windows, you have 3 alternatives: 1 - wait untill it stops the service (can take very long time). 2 - restart the server (your users might cry a bit). 3 - Try to kill the task using Task Manager (this might not work, depending on the service). /Johan Sara Woglom wrote: Please help, I can't start my server!! I was running a query, and it seemed to be hanging. After waiting about 15 minutes, I finally did a 'CTRL+BREAK' to abort the process. This happened about 3 times, and finally I decided to restart the MySQL service to see if that would help. Well, when I did that, the service wouldn't stop. Now when I go into the Services manager in Windows, the MySQL service is listed as 'Stopping,' and all the control buttons (stop, start, restart) are all grayed out. The problem is, the service is NOT stopping and I can't restart it doing a 'mysqld --console' either. What do I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to merge my tables?
Hi, I hope this is the right forum for this question. If not, I'm happy to get some tip on where to post this. My problem: I have like 20 tables of data and need to merge these, making a selection, (and dump it into a text file) to import into a reporting tool. The tables is like salesrep, value of sales, number of customers, and som series of codes. If I do a JOIN, I only get the salesreps that exist in that specific table, but new people are added and some have left the company. How can this be done?? A short example of the data: Table_1998: empl_id | sales98 | customers98 | etc98 | ... 1001 | 12659 | 123 | | ... 1002 | 103674 | 597 | hued | ... 1003 | 23589 | 314 | hjeoir| ... Table_1999: empl_id | sales99 | customers99 | etc99 | ... 1001 | 35678 | 213 | dwrer | ... 1002 | 125795 | 603 | freui | ... 1003 | 45678 | 343 | hfiwu | ... 1004 |8753 | 96 | poijo | ... Table_2000: empl_id | sales00 | customers00 | etc00 | ... 1001 | 97361 | 526 | urhfn | ... 1003 | 98716 | 649 | jdwoh | ... 1004 | 15872 | 147 | oijnm | ... Now I try to get the customersXX columns for every emloyee from these tables. What I would like to see in my result: empl_id | customers98 | customers99 | customers00 | ... 1001 | 123 | 213 | 526 | ... 1002 | 597 | 603 |NULL | ... 1003 | 314 | 343 | 649 | ... 1004 |NULL | 96 | 147 | ... I've tried everything and I'm out of clues. Can it be done?? If so, how??? Any help/tips are very welcome!! /Johan, Uppsala - Sweden -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to merge my tables?
No, I don't have that. There's about 5-10% change in employees ids from every year, and no one took any interest in this system before I got it in my lap... Would it help to have one?? Shaunak Kashyap wrote: Do you have a table that has *all* your employees ids (empl_id)? Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 1:13 PM To: mysql@lists.mysql.com Subject: How to merge my tables? Hi, I hope this is the right forum for this question. If not, I'm happy to get some tip on where to post this. My problem: I have like 20 tables of data and need to merge these, making a selection, (and dump it into a text file) to import into a reporting tool. The tables is like salesrep, value of sales, number of customers, and som series of codes. If I do a JOIN, I only get the salesreps that exist in that specific table, but new people are added and some have left the company. How can this be done?? A short example of the data: Table_1998: empl_id | sales98 | customers98 | etc98 | ... 1001 | 12659 | 123 | | ... 1002 | 103674 | 597 | hued | ... 1003 | 23589 | 314 | hjeoir| ... Table_1999: empl_id | sales99 | customers99 | etc99 | ... 1001 | 35678 | 213 | dwrer | ... 1002 | 125795 | 603 | freui | ... 1003 | 45678 | 343 | hfiwu | ... 1004 |8753 | 96 | poijo | ... Table_2000: empl_id | sales00 | customers00 | etc00 | ... 1001 | 97361 | 526 | urhfn | ... 1003 | 98716 | 649 | jdwoh | ... 1004 | 15872 | 147 | oijnm | ... Now I try to get the customersXX columns for every emloyee from these tables. What I would like to see in my result: empl_id | customers98 | customers99 | customers00 | ... 1001 | 123 | 213 | 526 | ... 1002 | 597 | 603 |NULL | ... 1003 | 314 | 343 | 649 | ... 1004 |NULL | 96 | 147 | ... I've tried everything and I'm out of clues. Can it be done?? If so, how??? Any help/tips are very welcome!! /Johan, Uppsala - Sweden -- 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: How to merge my tables?
I LOVE YOU!!! Great way of doing it. Never thought of creating that extra table and match against it... You really saved my day!! Now I just have to get rid of those rows containing nothing but NULL values (if I select years 2000 ... 2003 empl_id 1002 generates a row with all columns = NULL). I'll look in to it tomorrow, it's in the midddle of the night here and soon my backup system will start = no access to my server... Thanx again, this really got my out of my mindlock. Regards, /Johan Shaunak Kashyap wrote: OK. No problem. We can hopefully still make this work with a temporary table. The SQL will look something like this: CREATE TEMPORARY TABLE tmp_sales_rep SELECT empl_id FROM Table_1998 UNION SELECT empl_id FROM Table_1999 UNION SELECT empl_id FROM Table_2000 SELECT t.empl_id, t98.sales98, t99.sales99, t00.sales00 FROM tmp_sales_rep AS t LEFT JOIN Table_1998 AS t98 ON t.empl_id = t98.empl_id LEFT JOIN Table_1999 AS t99 ON t.empl_id = t99.empl_id LEFT JOIN Table_2000 AS t00 ON t.empl_id = t00.empl_id Hope that helps, Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 2:07 PM To: mysql@lists.mysql.com Subject: Re: How to merge my tables? No, I don't have that. There's about 5-10% change in employees ids from every year, and no one took any interest in this system before I got it in my lap... Would it help to have one?? Shaunak Kashyap wrote: Do you have a table that has *all* your employees ids (empl_id)? Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 1:13 PM To: mysql@lists.mysql.com Subject: How to merge my tables? Hi, I hope this is the right forum for this question. If not, I'm happy to get some tip on where to post this. My problem: I have like 20 tables of data and need to merge these, making a selection, (and dump it into a text file) to import into a reporting tool. The tables is like salesrep, value of sales, number of customers, and som series of codes. If I do a JOIN, I only get the salesreps that exist in that specific table, but new people are added and some have left the company. How can this be done?? A short example of the data: Table_1998: empl_id | sales98 | customers98 | etc98 | ... 1001 | 12659 | 123 | | ... 1002 | 103674 | 597 | hued | ... 1003 | 23589 | 314 | hjeoir| ... Table_1999: empl_id | sales99 | customers99 | etc99 | ... 1001 | 35678 | 213 | dwrer | ... 1002 | 125795 | 603 | freui | ... 1003 | 45678 | 343 | hfiwu | ... 1004 |8753 | 96 | poijo | ... Table_2000: empl_id | sales00 | customers00 | etc00 | ... 1001 | 97361 | 526 | urhfn | ... 1003 | 98716 | 649 | jdwoh | ... 1004 | 15872 | 147 | oijnm | ... Now I try to get the customersXX columns for every emloyee from these tables. What I would like to see in my result: empl_id | customers98 | customers99 | customers00 | ... 1001 | 123 | 213 | 526 | ... 1002 | 597 | 603 |NULL | ... 1003 | 314 | 343 | 649 | ... 1004 |NULL | 96 | 147 | ... I've tried everything and I'm out of clues. Can it be done?? If so, how??? Any help/tips are very welcome!! /Johan, Uppsala - Sweden -- MySQL General Mailing List For list
