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
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
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
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
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
what can cause a query to be much slower the second time?
This is using version: 5.0.18-log on linux. I have a stored procedure that takes .2 sec the first time, 12 the second, 12 the third. The data is unchanged. The relevant part of the procedure looks like this. ... drop temporary table if exists temptab; create temporary table temptab (index (id)) (SELECT id, col1, ... FROM table1); set @sql = concat(SELECT , cols, -- cols is an input string FROM table2 LEFT JOIN table3 on table2.id = table3.col2 left join temptab ON temptab.id=table3.col3 WHERE table2.col4=, quote(anotherinput), ORDER by col5); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; I doubt that the exact form of the query makes much difference. In fact, the same thing happens with two different queries I tried, but both using a temporary table built just before the query. That was intended to improve performance - and did the first time. It's not that dropping and creating the table is expensive. I can do that over and over again in approximately 0 sec. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about mysql_use_result()
http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html says you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for. My question is: why? In fact I thought that the normal interface for a database would show a screen full of results and then let you decide whether to go on to the next screen or quit. It seems crazy that if you do a query that gives a million results you have to retrieve them all. BTW I tried searching for an answer in the list archives but the search there doesn't seem to work very well. I type in mysql_use_result() and get back messages that don't mention it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql_use_result()
Paul DuBois writes: At 17:26 -0700 10/6/03, Don Cohen wrote: http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html says you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for. My question is: why? Because there is no provision in the client/server protocol whereby the client can interrupt a transfer from the server that has started. Perhaps I misunderstood the doc but I thought by using _use_result I was asking for the server not to send the next tuple until I asked for it. Otherwise what's all this stuff about using less space cause you only have to store one tuple at a time, and the stuff about not using this method if you might want to wait for a long time in the middle which would cause locking problems? In fact I thought that the normal interface for a database would show a screen full of results and then let you decide whether to go on to the next screen or quit. It seems crazy that if you do a query that gives a million results you have to retrieve them all. If you're really making the server do all the work of retrieving a million rows, especially for an interactive program, you might consider rewriting the query. For example, with LIMIT. Anyway, if you call mysql_free_result(), it will take care of retrieving and discarding and unfetched rows. The whole point here is that I *DON'T* want the server to generate all those rows. But I don't necessarily know how many I'll need at the beginning. Perhaps in some cases it will be necessary to generate all the rows in order to return the first one (for instance, if I ask for them to be sorted), but for the queries I intend to use I'd hope it would be possible to generate n tuples in time o(n). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
