RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Hi, Or if you are interested in limiting the number of rows add a limit clause, so that entire resultset of entire table is not returned , I am not sure how will that be done , but i have seen some GUIs doing that, would look for a solution from group. Thanks Abhishek -Original Message- From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: 24 September 2012 04:58 To: fuller.art...@gmail.com; pownall...@gmail.com Cc: mysql@lists.mysql.com Subject: RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...; Possibly run your constructed query thru a regex expression e.g. String mydata = SELECT * from table WHERE ab;; Pattern pattern = Pattern.compile('WHERE'); Matcher matcher = pattern.matcher(mydata); if (matcher.find()) { //WHERE clause found proceed normally } else throw new Exception(WHERE clause not found); Martin __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sun, 23 Sep 2012 18:38:58 -0400 Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...; From: fuller.art...@gmail.com To: pownall...@gmail.com CC: mysql@lists.mysql.com Tim, I think you misunderstood the question. Daniel wants to block Select queries that ask for all rwows, and permit only queries that ask for some rows, as restricted by the Where clause. Unfortunately, I don't think that can be done. But I'm not certain of that; there might be a trick. Arthur www.artfulsoftware.com On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Even if you could block them, they would be easy to get around: SELECT * FROM tbl WHERE 1; If you have long running queries, you should investigate the reasons (other than lack of WHERE). * MyISAM locks the table for any writes. This prevents a SELECT from starting or a select can prevent the INSERT/UPDATE/DELETE from starting. Consider switching to InnoDB. * How big is the table? Why does the user want the whole table (if it is a plain SELECT *)? If you are doing Data Warehousing, consider summary tables. Let's see the big picture; I expect there is a way to solve the _real_ problem, which I guess is performance. -Original Message- From: Arthur Fuller [mailto:fuller.art...@gmail.com] Sent: Sunday, September 23, 2012 3:39 PM To: Tim Pownall Cc: mysql@lists.mysql.com Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...; Tim, I think you misunderstood the question. Daniel wants to block Select queries that ask for all rwows, and permit only queries that ask for some rows, as restricted by the Where clause. Unfortunately, I don't think that can be done. But I'm not certain of that; there might be a trick. Arthur www.artfulsoftware.com On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Am 23.09.2012 20:23, schrieb Luis Daniel Lucio Quiroz: Just wondering if is possible to block SELECT queries that doesnt have a WHERE statement within. no and the idea is broken by design what is wrong with a select * from table with small tbales having only a handful of records? how will you work with broken select count(*) from table;? signature.asc Description: OpenPGP digital signature
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote: Helo, Just wondering if is possible to block SELECT queries that doesnt have a WHERE statement within. LD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Thanks, Tim Pownall GNU/Linux Systems Monitoring 610-621-9712 pownall...@gmail.com
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
that does not change the fact that it is valid to NOT have a where statement and for me it sound plain stupid to think about blocking queries without where-statements - the application developers will hopefully kill any admin who finds a solution for this... * i maintain a cms-system since 10 years * mainmenu: select id,title from table order by sortfield; * and YES in the backend the is no single reson to reduce the result * and NO using a key would not make anything faster so why would there be a WHERE make any sense and why does someone like to break the application just for fun? Am 23.09.2012 21:50, schrieb Tim Pownall: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote: Helo, Just wondering if is possible to block SELECT queries that doesnt have a WHERE statement within. signature.asc Description: OpenPGP digital signature
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Tim, I think you misunderstood the question. Daniel wants to block Select queries that ask for all rwows, and permit only queries that ask for some rows, as restricted by the Where clause. Unfortunately, I don't think that can be done. But I'm not certain of that; there might be a trick. Arthur www.artfulsoftware.com On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote:
RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Possibly run your constructed query thru a regex expression e.g. String mydata = SELECT * from table WHERE ab;; Pattern pattern = Pattern.compile('WHERE'); Matcher matcher = pattern.matcher(mydata); if (matcher.find()) { //WHERE clause found proceed normally } else throw new Exception(WHERE clause not found); Martin __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sun, 23 Sep 2012 18:38:58 -0400 Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...; From: fuller.art...@gmail.com To: pownall...@gmail.com CC: mysql@lists.mysql.com Tim, I think you misunderstood the question. Daniel wants to block Select queries that ask for all rwows, and permit only queries that ask for some rows, as restricted by the Where clause. Unfortunately, I don't think that can be done. But I'm not certain of that; there might be a trick. Arthur www.artfulsoftware.com On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote: