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: list rows with no recent updates
One option would be to add a column to the table with a last_updated timestamp. Everytime you update the row, update the last_updated field with the current timestamp. Therefore you could just query the timestamp column to get recently updated rows (or not so recently updated) as you please. -- Adam Alkins || http://www.rasadam.com On 14 June 2010 16:02, MadTh madan.feedb...@gmail.com wrote: Hi, I ran a update command on around 2700 rows inside a mysql database table which has around 3000 table rows to change the ( say) price of each item ( with unique ID. unique product code). like: mysql UPDATE tbl_xyz set listprice='9.45' where prod_id='3069' and prod_code='a0071'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 How can I list rows with no recent updates ( or the once where the above updates were not done) or say with no updates in last 2 hours? Thank you.
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: setting the data directory.
Hi, There's a mysqld startup option --datadir=/path/to/data which you can use to alter your data directory. Simply adjust your startup scripts to suite. Regards, -Adam On 1/31/06, Scott Johnson [EMAIL PROTECTED] wrote: Hi All, I am trying to find out how to change the location of the data files. I have not yet found a generic entry for the my.conf file to set a data and log location. Can I move the exiting data directory form the mySQL tree and put a soft link to a new location on another partition? thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Adam Alkins http://www.rasadam.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Math Computations
Something like
?
$db = mysql_connect(localhost,database_user,password);
mysql_select_db (database_name);
$query = SELECT * FROM table_name;
$result = mysql_query($query);
echo Computers | Department | Price Per PC | Total Amountbr;
while ($row = mysql_fetch_row($result)){
$subtotal=$row[0]*$row[2];
$total+=$subtotal;
echo $row[0] | $row[1] | $row[2] | $subtotalBR;
$comptotal+=$row[0];}
}
echo $comptotal $total;
?
Obviously format it into a table would make sense, e.t.c.
Adam
- Original Message -
From: delz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, April 11, 2002 5:29 AM
Subject: Math Computations
Hi,
I have this table that looks like this
Computers | Department | Price per PC | TotalAmount |
15 | Marketing | $500.00| |
5 | Purchasing | $ 300.00 | |
I want this table to display the total price wherein Computer * Price Per
PC
= TotalAmount
and want to display the total no. of computer and the OverAll TotalAmount.
I want the output to look like this
Computers | Department | Price per PC | TotalAmount |
15 | Marketing | $500.00| $ 7500.00 |
5 | Purchasing | $ 300.00 | $ 900.00 |
- ---
20 $ 8400.00
Can anyone help me how to do this in mysql or php. I will surely
appreciate
any help I
can get.
Thanks.
Delz
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
