Security and statement advise

[Brian]

Hi All

I have 2 question, the first one I hope I don't start an argurment !

How secure is MySQL? I have been asked by a client to create a windows based
application that will control / transfer / edit over HTTP. The data will be
held on a MySQL server hosted on a standard IPSs server. I have been writing
apps to do this for a while, but this client will be holding data that will
be covered under the data protection act so they want to know how secure the
data will be.

So as a general rule how hard (or hopefully not easy) is MySQL to hack, or
how secure is it when it can be accessed by external apps? Dose it just rely
on the usernames and passwords?

The second question, I want some advise on the best way to run a query.

I have a list of product IDs, and I am trying to work out how many of each
of the products was ordered between 2 dates, I have done the following but
it take way to long to come back with the results as there could be up to
3000 products

SELECT product, description, SUM(IF(product = 'productID1', `ord`, 0)) AS
product1IDord, SUM(IF(product = 'productID2', `ord`, 0)) AS productID2ord,
SUM(IF(product = 'productID3', `ord`, 0)) AS productID3ord, (.and so on but
could be up to 3000 products) FROM theTable WHERE (timestamp >= '1234') AND
(timestamp <= '9876') GROUP BY product ORDER BY product

Thanks in advance

Brian

[noone]

Brian wrote:

Hi All

I have 2 question, the first one I hope I don't start an argurment !

How secure is MySQL? I have been asked by a client to create a windows based
application that will control / transfer / edit over HTTP. The data will be
held on a MySQL server hosted on a standard IPSs server. I have been writing
apps to do this for a while, but this client will be holding data that will
be covered under the data protection act so they want to know how secure the
data will be.

Before I would procede with the current path, I would want to search the
web thoroughly to see what others have said. For every method of
protection in most any system are various ways to hack it. For "data
protection act" related data, I would probably want to use a real
database engine.

search the web for MySQL sql injection security


So as a general rule how hard (or hopefully not easy) is MySQL to hack, or
how secure is it when it can be accessed by external apps? Dose it just rely
on the usernames and passwords?
This will all depend upon how well/how much your ISP is willing to lock
the system down to prevent it from being hacked. I would not bet my CC#
on most of them being that secure as they must allow others to use those
servers. You want it secure, have the "customer" host it themselves and
hire the best security people to design and maintain it. Security
cannot be "bolted on", everything from the application to the OS and
database must be designed from the ground up with security as it's core
not an after-thought.


The second question, I want some advise on the best way to run a query.

I have a list of product IDs, and I am trying to work out how many of each
of the products was ordered between 2 dates, I have done the following but
it take way to long to come back with the results as there could be up to
3000 products

SELECT product, description, SUM(IF(product = 'productID1', `ord`, 0)) AS
product1IDord, SUM(IF(product = 'productID2', `ord`, 0)) AS productID2ord,
SUM(IF(product = 'productID3', `ord`, 0)) AS productID3ord, (.and so on but
could be up to 3000 products) FROM theTable WHERE (timestamp >= '1234') AND
(timestamp <= '9876') GROUP BY product ORDER BY product

Add a status field to the record. doing what you are doing will result
in 3000+ fields in the result with a bunch of zeros.. Probably not what
you want.

select product, description, count(product)
from thetable
where timestamp between [date1] and [date2]
and status='ord'
group by product,description order by product;

Case and Point (small dataset - no date field but the effects are the
same...

mysql> select d,
-> sum(if(d='prod1',1,0)) as prod1,
-> sum(if(d='prod2',1,0)) as prod2,
-> sum(if(d='prod3',1,0)) as prod3,
-> sum(if(d='prod4',1,0)) as prod4,
-> sum(if(d='prod5',1,0)) as prod5,
-> sum(if(d='prod6',1,0)) as prod6
-> from t group by d;
+-------+-------+-------+-------+-------+-------+-------+
| d | prod1 | prod2 | prod3 | prod4 | prod5 | prod6 |
+-------+-------+-------+-------+-------+-------+-------+
| prod1 | 3 | 0 | 0 | 0 | 0 | 0 |
| prod2 | 0 | 1 | 0 | 0 | 0 | 0 |
| prod3 | 0 | 0 | 2 | 0 | 0 | 0 |
| prod4 | 0 | 0 | 0 | 1 | 0 | 0 |
+-------+-------+-------+-------+-------+-------+-------+
4 rows in set (0.01 sec)

mysql> select d,count(*) from t group by d;
+-------+----------+
| d | count(*) |
+-------+----------+
| prod1 | 3 |
| prod2 | 1 |
| prod3 | 2 |
| prod4 | 1 |
+-------+----------+
4 rows in set (0.01 sec)


Thanks in advance

Brian

[Bill Karwin]

"noone" <no***@nowhere.com> wrote in message
news:Wa****************@newssvr11.news.prodigy.com ...

search the web for MySQL sql injection security

Wouldn't any RDBMS be vulnerable to SQL injection if the application is
designed to allow it?
As I understand the idea, SQL injection is a problem in the application, not
the RDBMS.
http://en.wikipedia.org/wiki/SQL_injection

There's a page in the MySQL manual that covers security issues:
http://dev.mysql.com/doc/refman/5.0/en/security.html and its subsections.

I agree that security must include attention to all components of a system.
The security is only as strong as its weakest link. The security plan must
include constant monitoring and logging, too. You can't rely on any
software to be immune to attack forever.

That said, it's a good start to run the RDBMS behind a firewall and disallow
any access to it except directly from the HTTP server hosting the web apps.
If one relies on the security of MySQL to protect the data, it's too late!

And there are books specifically about database security, for example "The
Database Hacker's Handbook: Defending Database Servers" rates 5 stars out of
5 at Amazon.com.
http://www.amazon.com/gp/product/0764578014/
I haven't read it, but it's on my wishlist. :-)

Finally, hacking experts like Kevin Mitnick say that "social hacking" is
harder to prevent than technological hacking. See
http://www.kevinmitnick.com/ and his books like "The Art of Intrusion". One
should not overlook the need to establish policies to define who has access
to the sensitive data within your organization, how they can grant access to
others, and how their access is logged. Training the staff in these
policies is important.

Regards,
Bill K.

[Bill Karwin]

"noone" <no***@nowhere.com> wrote in message
news:Wa****************@newssvr11.news.prodigy.com ...

SELECT product, description, SUM(IF(product = 'productID1', `ord`, 0)) AS
product1IDord, SUM(IF(product = 'productID2', `ord`, 0)) AS
productID2ord, SUM(IF(product = 'productID3', `ord`, 0)) AS
productID3ord, (.and so on but could be up to 3000 products) FROM
theTable WHERE (timestamp >= '1234') AND (timestamp <= '9876') GROUP BY
product ORDER BY product

select product, description, count(product)
from thetable
where timestamp between [date1] and [date2]
and status='ord'
group by product,description order by product;

I assume the OP's queryuses SUM because it's summing values in the `ord`
column. He uses back-ticks instead of single-quotes, therefore ord is a
column. One cannot assume that ord always contains the value 1, so one must
use SUM instead of COUNT. Otherwise I fully agree with you that this should
be done in one column, not 3000 columns! :)

SELECT product, description, SUM(`ord`) AS productOrd
FROM theTable
WHERE `timestamp` BETWEEN '1234' AND '9876'
GROUP BY product, description
ORDER BY product

Regards,
Bill K.

[Brian]

>>> SELECT product, description, SUM(IF(product = 'productID1', `ord`, 0))

AS product1IDord, SUM(IF(product = 'productID2', `ord`, 0)) AS
productID2ord, SUM(IF(product = 'productID3', `ord`, 0)) AS
productID3ord, (.and so on but could be up to 3000 products) FROM
theTable WHERE (timestamp >= '1234') AND (timestamp <= '9876') GROUP BY
product ORDER BY product

select product, description, count(product)
from thetable
where timestamp between [date1] and [date2]
and status='ord'
group by product,description order by product;

I assume the OP's queryuses SUM because it's summing values in the `ord`
column. He uses back-ticks instead of single-quotes, therefore ord is a
column. One cannot assume that ord always contains the value 1, so one
must use SUM instead of COUNT. Otherwise I fully agree with you that this
should be done in one column, not 3000 columns! :)

SELECT product, description, SUM(`ord`) AS productOrd
FROM theTable
WHERE `timestamp` BETWEEN '1234' AND '9876'
GROUP BY product, description
ORDER BY product

Regards,
Bill K.

Hi Guys

Thanks for you replies, sorry for the delay in replying, PC problems and got
sided tracked
by another project

I will try and explain a little better what i and trying to do with this
statement

I need to create a SQL statement that brings back a list and sum of all
products ordered between two dates but only for a given supplier, if non
were ordered then sum = 0
I have a table of PRODUCTS and table of ORDERS

SELECT * FROM products WHERE supplier = X < this gives me a list of all the
products that are supplied by a given supplier

Now I need to say, select and sum the products from a given supplier between
two dates and return a list of the product name and how many ordered, if
none then would be 0

eg

Product | Total
Product1 | 78
Product2 | 90
Product3 | 78
Product4 | 0
Product5 | 67
Product6 | 0
....

I have had a play round, but just can' seem to work out what I should be
doing
Hope this explains it better

Cheers

Brian

[Bill Karwin]

Brian wrote:

Now I need to say, select and sum the products from a given supplier between
two dates and return a list of the product name and how many ordered, if
none then would be 0

eg

Product | Total
Product1 | 78
Product2 | 90
Product3 | 78
Product4 | 0
Product5 | 67
Product6 | 0
...

Okay, thanks, that is good new information about your query.
You need to use an OUTER JOIN if you want all products, including those
for which there are no orders.

One of the tricks of outer joins is that if you need to restrict the
rows in the left-hand table (speaking of left outer joins here), you can
put the conditions in the WHERE clause. But if you need to restrict the
rows in the right-hand table, you need to put the conditions in the ON
clause, because if you use the WHERE clause, it'll only match rows where
the right-hand table is not null.

SELECT p.product, SUM(o.`ord`) AS Total
FROM products AS p LEFT OUTER JOIN orders AS o
ON (p.product = o.product
AND o.`timestamp` BETWEEN '1234' AND '9876')
WHERE p.supplier = X
GROUP BY p.product
ORDER BY p.product

Regards,
Bill K.