Re: opening a server to generalized queries but not too far
Raj Shekhar writes: One option here might be to use mysql proxy as a man-in-the-middle and filter out unwanted queries... This seems more or less the same as what I'm doing now with php. The same question applies there - what would you look for in your filter? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: opening a server to generalized queries but not too far
In infinite wisdom don-mysq...@isis.cs3-inc.com (Don Cohen) wrote: The question is what I have to prohibit in order to prevent either updates or access to other tables, or perhaps other things that I should be worried about but haven't yet thought of. One option here might be to use mysql proxy as a man-in-the-middle and filter out unwanted queries. You can find an example on how to do this with mysql proxy on the mysql forge wiki http://forge.mysql.com/tools/tool.php?id=108 (more stuff http://forge.mysql.com/tools/search.php?t=tagk=mysqlproxy) (in case you do not know mysql proxy, see http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html?page=1 ) -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
opening a server to generalized queries but not too far
This seems like a topic that must have been studied, but I'm having trouble figuring out what to search for in Google, since the usual discussion of sql injection is not what I'm looking for here. If anyone knows of references that discuss the issue, I'd like to see them. I'm also interested in answers for other RDBMS's, and I imagine that details of implementation may matter, but my immediate primary interest is mysql used from php. I want to allow web users to make a very wide variety of queries, but limited to queries (no updates, redefinitions, etc), and limited to a fixed set of tables - let's suppose one table with no joins, and perhaps a few other restrictions. I propose to send queries of the following form from php to the DB: select colspec from fixedtable where wherespec group by groupspec order by orderspec The user gets to supply all of the spec's. So, as an example, I want the user to be able to do select max(col1) from fixedtable group by col2 The question is what I have to prohibit in order to prevent either updates or access to other tables, or perhaps other things that I should be worried about but haven't yet thought of. So far I have identified at least one problem, which is subqueries such as select 1 from ... where exists (select 1 from othertable ...) These can tell the attacker about other data he should not be able to read. At the moment I plan to simply disallow inputs containing the string select (case insensitive). Is there any way to get a select statement to execute other statements, such as insert, delete, drop? I believe that ; is not a problem because a single request from php to mysql containing multiple statements will result in a syntax error. If I subject the inputs to mysql_real_escape_string then the user will not be able to use quotes, which will prevent use of string constants. What more could an attacker do if I don't escape the inputs? Finally, suppose I want to limit access to the table to the rows where col1=value1. If I just add that to wherespec what can an attacker do to read other rows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: opening a server to generalized queries but not too far
Sounds like you just want to GRANT access to specific tables (and with limited commands), which is exactly what MySQL's privilege system does. Refer to http://dev.mysql.com/doc/refman/5.1/en/grant.html http://dev.mysql.com/doc/refman/5.1/en/grant.htmlFor example, you can grant only SELECT privileges to a specific table for a specific user. -- Adam Alkins || http://www.rasadam.com On 16 June 2010 14:17, Don Cohen don-mysq...@isis.cs3-inc.com wrote: This seems like a topic that must have been studied, but I'm having trouble figuring out what to search for in Google, since the usual discussion of sql injection is not what I'm looking for here. If anyone knows of references that discuss the issue, I'd like to see them. I'm also interested in answers for other RDBMS's, and I imagine that details of implementation may matter, but my immediate primary interest is mysql used from php. I want to allow web users to make a very wide variety of queries, but limited to queries (no updates, redefinitions, etc), and limited to a fixed set of tables - let's suppose one table with no joins, and perhaps a few other restrictions. I propose to send queries of the following form from php to the DB: select colspec from fixedtable where wherespec group by groupspec order by orderspec The user gets to supply all of the spec's. So, as an example, I want the user to be able to do select max(col1) from fixedtable group by col2 The question is what I have to prohibit in order to prevent either updates or access to other tables, or perhaps other things that I should be worried about but haven't yet thought of. So far I have identified at least one problem, which is subqueries such as select 1 from ... where exists (select 1 from othertable ...) These can tell the attacker about other data he should not be able to read. At the moment I plan to simply disallow inputs containing the string select (case insensitive). Is there any way to get a select statement to execute other statements, such as insert, delete, drop? I believe that ; is not a problem because a single request from php to mysql containing multiple statements will result in a syntax error. If I subject the inputs to mysql_real_escape_string then the user will not be able to use quotes, which will prevent use of string constants. What more could an attacker do if I don't escape the inputs? Finally, suppose I want to limit access to the table to the rows where col1=value1. If I just add that to wherespec what can an attacker do to read other rows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@adamalkins.com
Re: opening a server to generalized queries but not too far
Adam Alkins writes: Sounds like you just want to GRANT access to specific tables (and with limited commands), which is exactly what MySQL's privilege system does. How about this part? Finally, suppose I want to limit access to the table to the rows where col1=value1. If I just add that to wherespec what can an attacker do to read other rows? The http request I have in mind will be something like https://server.foo.com?user=johnpassword=wxyz;... and the resulting query something like select ... from table where user=john and ... (I will first have verified the password.) It seems I'd have to create a separate mysql user for each user in my table, and perhaps also a separate separate table (or view?) for that user to be allowed to read. I suppose the php process could create the table/view, create the user, then connect as the user to execute the query, then remove the user. Even if this turns out to be the best solution, I'm interested in the answer to the original question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: opening a server to generalized queries but not too far
MySQL doesn't have row level permissions, but this is what VIEWS are for. If you only want access to specific rows, create a view with that subset of data. You can create a function (privilege bound) to create the view to make this more dynamic. If you want direct access to the database, then you will need multiple MySQL users. I don't quite get the purpose of what you're trying to do through a PHP script; I see little reason why you can't use some client level security to facilitate data access, especially if you're going to implement dynamic creation of users in the PHP script itself (which would be rather weak security imho). -- Adam Alkins || http://www.rasadam.com On 16 June 2010 14:48, Don Cohen don-mysq...@isis.cs3-inc.com wrote: Adam Alkins writes: Sounds like you just want to GRANT access to specific tables (and with limited commands), which is exactly what MySQL's privilege system does. How about this part? Finally, suppose I want to limit access to the table to the rows where col1=value1. If I just add that to wherespec what can an attacker do to read other rows? The http request I have in mind will be something like https://server.foo.com?user=johnpassword=wxyz;... and the resulting query something like select ... from table where user=john and ... (I will first have verified the password.) It seems I'd have to create a separate mysql user for each user in my table, and perhaps also a separate separate table (or view?) for that user to be allowed to read. I suppose the php process could create the table/view, create the user, then connect as the user to execute the query, then remove the user. Even if this turns out to be the best solution, I'm interested in the answer to the original question.
RE: opening a server to generalized queries but not too far
-Original Message- From: Don Cohen [mailto:don-mysq...@isis.cs3-inc.com] The http request I have in mind will be something like https://server.foo.com?user=johnpassword=wxyz;... and the resulting query something like select ... from table where user=john and ... (I will first have verified the password.) For the love of God and all that is holy, do NOT put the user/pass on the URL like that!! Do something like this instead: http://us.php.net/manual/en/features.http-auth.php Or use mod_auth_mysql to maintain your 'authorized' users to your page. And as Adam beat me to, use a VIEW to expose ONLY the columns and joins you want. This is also a good time to normalize the data and column names so that you don't expose what their internal names are. http://dev.mysql.com/doc/refman/5.0/en/create-view.html http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views http://www.devshed.com/c/a/MySQL/Views-and-More-in-MySQL-50/ But also has he said, I don't see what you're trying to accomplish. If someone is technically literate to format SQL statements, then just give them a read-only account to the mysql (or view) directly. Let them use their own GUI tool like SQLYog or whatever -- it will be far more robust than anything you can write yourself. If you're trying to do some reports, then just code up the reports and use select boxes for the options you want someone to choose. Use jQuery and table_sorter plugin and you're done. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: opening a server to generalized queries but not too far
Daevid Vincent writes: For the love of God and all that is holy, do NOT put the user/pass on the URL like that!! What's so unholy (or even unwise) about it? Or use mod_auth_mysql to maintain your 'authorized' users to your page. Why is this so much better? In my case it's worse cause I don't want this to be interactive. I want to install something on a user's machine that will access his data without him having to do anything. The url is to be used by a program, not a person on a browser. And as Adam beat me to, use a VIEW to expose ONLY the columns and joins you want. This is also a good time to normalize the data and column names so that you don't expose what their internal names are. So far I don't like this solution. But also has he said, I don't see what you're trying to accomplish. If I'm trying to let a large number of users each access his own data and not everyone else's in a very flexible way, in particular, allowing selection using functions, aggregations, filters. someone is technically literate to format SQL statements, then just give them a read-only account to the mysql (or view) directly. Let them use their own GUI tool like SQLYog or whatever -- it will be far more robust than anything you can write yourself. In this case there may be a lot of users but the queries are likely to be written by a small number. If you're trying to do some reports, then just code up the reports and use select boxes for the options you want someone to choose. Use jQuery and table_sorter plugin and you're done. I can't predict what options will be needed. And this seems much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: opening a server to generalized queries but not too far
On Wed, June 16, 2010 14:47, Don Cohen wrote: Daevid Vincent writes: For the love of God and all that is holy, do NOT put the user/pass on the URL like that!! What's so unholy (or even unwise) about it? The username and password shows up in logs on the server and in the browser's cache since it is part of the page's address. Anyone who has access to either will get them. Remember, browser's cache history. Or use mod_auth_mysql to maintain your 'authorized' users to your page. Why is this so much better? In my case it's worse cause I don't want this to be interactive. I want to install something on a user's machine that will access his data without him having to do anything. The url is to be used by a program, not a person on a browser. And as Adam beat me to, use a VIEW to expose ONLY the columns and joins you want. This is also a good time to normalize the data and column names so that you don't expose what their internal names are. So far I don't like this solution. But also has he said, I don't see what you're trying to accomplish. If I'm trying to let a large number of users each access his own data and not everyone else's in a very flexible way, in particular, allowing selection using functions, aggregations, filters. someone is technically literate to format SQL statements, then just give them a read-only account to the mysql (or view) directly. Let them use their own GUI tool like SQLYog or whatever -- it will be far more robust than anything you can write yourself. In this case there may be a lot of users but the queries are likely to be written by a small number. If you're trying to do some reports, then just code up the reports and use select boxes for the options you want someone to choose. Use jQuery and table_sorter plugin and you're done. I can't predict what options will be needed. And this seems much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mussa...@csz.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: opening a server to generalized queries but not too far
-Original Message- From: Don Cohen [mailto:don-mysq...@isis.cs3-inc.com] Sent: Wednesday, June 16, 2010 2:48 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: RE: opening a server to generalized queries but not too far Daevid Vincent writes: For the love of God and all that is holy, do NOT put the user/pass on the URL like that!! What's so unholy (or even unwise) about it? Oh my goodness, where to begin... Well barring the fact that it'll be in the user's cache and browser history, you are sending this information in plain text across the internet, open to ANYONE on the way who wishes to packet sniff. Or use mod_auth_mysql to maintain your 'authorized' users to your page. Why is this so much better? In my case it's worse cause I don't want this to be interactive. I want to install something on a user's machine that will access his data without him having to do anything. The url is to be used by a program, not a person on a browser. One has nothing to do with the other. Mod_auth_mysql will serve as the user/password prompt and authentication between the user and the page you're creating. It is just an easy way for YOU to manage the users. If you have their user/password in a database -- presumably you already do, then leverage that. That's all this apache module will do. It's no different than you having to use $_GET['user'] and $_GET['password'] from your URL, querying the DB and granting some data back, except you've removed the horrific thought of forcing the user to expose their vitals in plain text in the URL/GET/POST/whatever. And as Adam beat me to, use a VIEW to expose ONLY the columns and joins you want. This is also a good time to normalize the data and column names so that you don't expose what their internal names are. So far I don't like this solution. Interesting. Perhaps you don't understand it? A view seems to suit your needs very well from what you've explained. It allows a READ ONLY access to your data. It prevents exposing your column/table names. It normalizes your data so your customers get it without having to jump through hoops and knowing intimate details about your schema. Pretty much this is why VIEWs were invented. But also has he said, I don't see what you're trying to accomplish. If I'm trying to let a large number of users each access his own data and not everyone else's in a very flexible way, in particular, allowing selection using functions, aggregations, filters. Right. And for YOU to code all that up in some fancy web GUI is going to be a nightmare and will never be all things to all people that you desire. So you either give people a GUI front end with select boxes and parameters, OR you give them some direct SQL access to a VIEW, OR you give them some XML/JSON/Excel/CVS/whatever output that they can then pull into their tool of choice to manipulate. Why not provide daily SQL dumps of their normalized data to your users and let them run their reports -- if they're trying to run SQL queries themselves? someone is technically literate to format SQL statements, then just give them a read-only account to the mysql (or view) directly. Let them use their own GUI tool like SQLYog or whatever -- it will be far more robust than anything you can write yourself. In this case there may be a lot of users but the queries are likely to be written by a small number. If you're trying to do some reports, then just code up the reports and use select boxes for the options you want someone to choose. Use jQuery and table_sorter plugin and you're done. I can't predict what options will be needed. Well you don't have to. You just make select boxes and such for each field that someone would want to select on and various filters. I bet you can cover 80% of your users with that if not all of them. And this seems much easier. Oh grasshopper... So Naïve. :) Here's what I think will happen -- and you can tell me nah nah nah nah in 2 years when you finish writing this uber-tool that does all this fancy SQL magic manipulation. It will either still fall short of what you wanted it to do b/c it's too complicated to code, OR it will be too complicated to use and nobody will understand it enough to use it. But hey, more power to ya Don if you want to tackle it, go ahead. I would suggest maybe looking at http://www.phpmyadmin.net Daevid. http://daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: opening a server to generalized queries but not too far
Daevid Vincent writes: For the love of God and all that is holy, do NOT put the user/pass on the URL like that!! What's so unholy (or even unwise) about it? Oh my goodness, where to begin... Well barring the fact that it'll be in the user's cache and browser It won't because this url is not intended to be used in the browser but in an application. history, you are sending this information in plain text across the internet, open to ANYONE on the way who wishes to packet sniff. You might notice that the url started with https so this is also not true. Mod_auth_mysql will serve as the user/password prompt and authentication between the user and the page you're creating. It is just an easy way for YOU to manage the users. If you have their user/password in a database -- presumably you already do, then leverage that. That's all this apache module will do. The user prompt relies on a browser, right? This url is not being used by a browser. It's no different than you having to use $_GET['user'] and $_GET['password'] from your URL, querying the DB and granting some data back, except you've removed the horrific thought of forcing the user to expose their vitals in plain text in the URL/GET/POST/whatever. No prompt in a browser that's not being used. No plain text if I use https. Interesting. Perhaps you don't understand it? A view seems to suit your needs very well from what you've explained. It allows a READ ONLY access to your data. It prevents exposing your column/table names. It normalizes your data so your customers get it without having to jump through hoops and knowing intimate details about your schema. Pretty much this is why VIEWs were invented. You want me to create a view for every user, right? Or else you want me to create a user and a view every time a user accesses the server and then delete them? Either way seems ridiculously complicated compared to adding where user = input The user only has to know about the columns I'm advertising, but if he wants to look at (or look for) others I don't care. How does the view simplify matters for him? He still has to write pretty much the same query. Right. And for YOU to code all that up in some fancy web GUI is going to be a nightmare and will never be all things to all people that you desire. So you either give people a GUI front end with select boxes and parameters, OR you give them some direct SQL access to a VIEW, OR you give them some XML/JSON/Excel/CVS/whatever output that they can then pull into their tool of choice to The advantage of the direct sql over xml is that they have access to things like select max(col), where conditions, etc. so they can get back much less data and the results they want rather than having to get back much more and then still have to compute the results they want. So far I don't see that my query allowing colspec,wherespec, etc. is worse in any way than any of the other suggestions, and I see ways in which it's better than all of them. So far manipulate. Why not provide daily SQL dumps of their normalized data to your users and let them run their reports -- if they're trying to run SQL queries themselves? First, why do you assume these are daily reports? They are not. This is an application that displays data that may be changing much faster than that. The user runs it, perhaps selects the data/display he wants to see, then gets the data for that display up to the minute. Then if he wishes, he makes another choice. If he wants to see a small summary of a large amount of data, this is what databases are for. I want the max or average to be computed in the database. I don't want him to retrieve the entire database and then compute that at the client. Well you don't have to. You just make select boxes and such for each field that someone would want to select on and various filters. I bet you can cover 80% of your users with that if not all of them. You want to tell me what select boxes to supply? There are - lots of different where conditions that might be used - lots of FUNCTIONS of the data to apply (which might be inputs to aggregations and order by's) And this seems much easier. Oh grasshopper... So Naïve. :) Well then perhaps you should supply either some actual technical arguments that show me what's wrong with what I propose, or some references that contain those arguments. Here's what I think will happen -- and you can tell me nah nah nah nah in 2 years when you finish writing this uber-tool that does all this fancy SQL magic manipulation. It will either still fall The sql manipulation is to be done by mysql - so I'm now finished writing that part. short of what you wanted it to do b/c it's too complicated to code, OR it will be too complicated to use and nobody will understand it enough to use it. If what I have described is too complicated to use then so is mysql. I expect most end users will run what
