Re: Indexed Query examining too many rows!
Hi, As you can see in my query, % is not in the beginning. Once again, it is : select * from DataIndex where (searchKey like 'A%') order by searchKey limit 10 where searchKey has a btree on it. As Peter was saying, percent in the beginning does a full table scan as expected. Thanks. On Mon, Feb 13, 2012 at 12:57 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2/12/2012 4:40 PM, Reindl Harald wrote: Am 12.02.2012 23:25, schrieb Cabbar Duzayak: Hi All, I have a table with a btree index on its searchKey column, and when I send a simple query on this table: explain select * from DataIndex where (searchKey like 'A%') order by searchKey limit 10 rows is returning 59548 and it tells me that it is using the searchKey index. Also, a select count(*) on this table returns 32104 rows, i.e. select count(*) from DataIndex where searchKey like 'a%' - gives 32104 as its result Am I doing something wrong here? Given that the searched column is indexed, shouldn't it examine way less rows? LIKE does not benefit from keys! It does if the wildcard is not at the front, as indicated at http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-2719/ ... *When MySQL uses indexes* ... When you use a LIKE that doesn't start with a wildcard. SELECT * FROM table_name WHERE key_part1 LIKE 'jani%' ... PB - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: weird difference in workbench and CLI query
On 2/13/2012 10:18 AM, James W. McNeely wrote: When I run this query in workbench: select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date', s.ExamDate 'SJH Exam Date' from chestcon_log c left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum left join sjhreports_ s on c.acc_number = s.AccessionNumber WHERE c.timestamp_exam= CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL '-1' DAY) AS DATETIME) AND c.timestamp_exam= CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL '-14' DAY) AS DATETIME) I get this: 7330565 NULL 2012-02-01 6604419 2011-01-25 NULL but when I run the same query in a shell script, on a Linux box or OS X, I get this: 7330565 NULL2012-02-01 6604419 NULLNULL What are the MySQL versions and sql_mode settings on the two boxes? PB - I also tried echo this and piping it into MySQL with no shell script. Same result. Why does the date eval to null in CLI? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: weird difference in workbench and CLI query
When I run this query in workbench:
select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date',
s.ExamDate 'SJH Exam Date' from chestcon_log c
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
left join sjhreports_ s on c.acc_number = s.AccessionNumber
WHERE
c.timestamp_exam = CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL
'-1' DAY) AS DATETIME) AND c.timestamp_exam =
CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL '-14' DAY) AS
DATETIME)
I get this:
7330565 NULL 2012-02-01
66044192011-01-25 NULL
but when I run the same query in a shell script, on a Linux box or OS X, I
get this: 7330565 NULL2012-02-01
6604419 NULLNULL
I see mixes of single ' and double ... And this is the line that fails, the
only occurance of :
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
Which my crystal ball tells me is probably a sign of improper escapes in shell
scripts. What happens if you replace that line with:
left join exams e on CONCAT('000',c.acc_number) = e.LastWordAccNum
--
Rik Wasmus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: weird difference in workbench and CLI query
Rik,
Your Crystal Ball was right! How did I miss that? You get the brownie point for
the day. Thanks!
I knew it wasn't version discrepancy because workbench ssh's into the DB, and
the script does as well, so it is running it on the native client in both
cases. My sysadmin was saying it was version discrepancy as well and I was just
sure that wasn't it.
Jim McNeely
On Feb 13, 2012, at 9:11 AM, Rik Wasmus wrote:
When I run this query in workbench:
select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date',
s.ExamDate 'SJH Exam Date' from chestcon_log c
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
left join sjhreports_ s on c.acc_number = s.AccessionNumber
WHERE
c.timestamp_exam = CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL
'-1' DAY) AS DATETIME) AND c.timestamp_exam =
CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL '-14' DAY) AS
DATETIME)
I get this:
7330565 NULL 2012-02-01
6604419 2011-01-25 NULL
but when I run the same query in a shell script, on a Linux box or OS X, I
get this: 7330565NULL2012-02-01
6604419 NULLNULL
I see mixes of single ' and double ... And this is the line that fails, the
only occurance of :
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
Which my crystal ball tells me is probably a sign of improper escapes in
shell
scripts. What happens if you replace that line with:
left join exams e on CONCAT('000',c.acc_number) = e.LastWordAccNum
--
Rik Wasmus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
a sql injection attempt
My logs shows that we have tried with a SQL Injection attempt, but our engine has detected and avoided it but I am just curious, what are these SQL statements are intending to achieve? SELECT * FROM lecturer WHERE recID='25 ' and exists (select * from sysobjects) and ''='' ORDER BY EntryDate DESC and SELECT * FROM lecturer WHERE recID='25' and char(124)+user+char(124)=0 and '%'='' ORDER BY EntryDate DESC If these were let in, what would have happened? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a sql injection attempt
On 13/02/2012 21:48, Haluk Karamete wrote: My logs shows that we have tried with a SQL Injection attempt, but our engine has detected and avoided it but I am just curious, what are these SQL statements are intending to achieve? SELECT * FROM lecturer WHERE recID='25 ' and exists (select * from sysobjects) and ''='' ORDER BY EntryDate DESC and SELECT * FROM lecturer WHERE recID='25' and char(124)+user+char(124)=0 and '%'='' ORDER BY EntryDate DESC If these were let in, what would have happened? Nothing on MySQL - however, if the back end was an MS SQL server then the first query would prove that the user had access to the sysobjects table (ie wasn't constrained within a view, etc). The second is - the char(124) evaluates to |user|=0. I'm not sure what this one does, tbh. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a sql injection attempt
sorry, i overlooked that this IS a mysql mailing-list and we are running ms-sql in this particular case. good catch... I'd appreciate any insight though. On Mon, Feb 13, 2012 at 1:56 PM, Gary Smith shady...@l33t-d00d.co.uk wrote: On 13/02/2012 21:48, Haluk Karamete wrote: My logs shows that we have tried with a SQL Injection attempt, but our engine has detected and avoided it but I am just curious, what are these SQL statements are intending to achieve? SELECT * FROM lecturer WHERE recID='25 ' and exists (select * from sysobjects) and ''='' ORDER BY EntryDate DESC and SELECT * FROM lecturer WHERE recID='25' and char(124)+user+char(124)=0 and '%'='' ORDER BY EntryDate DESC If these were let in, what would have happened? Nothing on MySQL - however, if the back end was an MS SQL server then the first query would prove that the user had access to the sysobjects table (ie wasn't constrained within a view, etc). The second is - the char(124) evaluates to |user|=0. I'm not sure what this one does, tbh. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a sql injection attempt
Gary, you've mentioned that the user would have had access to the sysobjects Let's assume he did. The page that this attempt occurred is hard-wired to display a single record in detail view. In the code, I have a bunch of echo $row-title kind of statements... I'm even more curious now; what kind of goodies this evil user would have gotten with having access to the sysobjects from the query string? I mean how would my page display sysobjects data when I don't have anything to do with echo sysobjects stuff? can you shed some light maybe? thx. On Mon, Feb 13, 2012 at 1:56 PM, Gary Smith shady...@l33t-d00d.co.uk wrote: On 13/02/2012 21:48, Haluk Karamete wrote: My logs shows that we have tried with a SQL Injection attempt, but our engine has detected and avoided it but I am just curious, what are these SQL statements are intending to achieve? SELECT * FROM lecturer WHERE recID='25 ' and exists (select * from sysobjects) and ''='' ORDER BY EntryDate DESC and SELECT * FROM lecturer WHERE recID='25' and char(124)+user+char(124)=0 and '%'='' ORDER BY EntryDate DESC If these were let in, what would have happened? Nothing on MySQL - however, if the back end was an MS SQL server then the first query would prove that the user had access to the sysobjects table (ie wasn't constrained within a view, etc). The second is - the char(124) evaluates to |user|=0. I'm not sure what this one does, tbh. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
