1

My group and I are developing a multi agent system (MAS), a system that is supposed to conduct statistics on specific fields of the agents' databases using semantic search and ontology (the user chooses the fields to be considered).

For example, the user might want to know the number of people stored in the database that are male, over 20 years old, lives in NYC, blood type: A, and are single.

So I was wondering if there is a way to write the query in MySQL(MySQL 5.7.10.0) that returns the number of matching tuples (matches the user's selection and conditions) in the database, or if the jsc.jar in JAVA (java statistical classes) or any other JAVA jar can perform the required operation. I know that I should use the "count(*)" in the query, but I'm not sure how to build the "where" part of the query (that relies on the user's selection of fields) at runtime.

I'm looking for a way that a query builds itself (to be specific the "where" part) according to what the user chooses during runtime. Is it possible?

4
  • 1
    What you seek goes under the name of dynamic SQL. The query submitted to the database engine is just a string. You can build this in the application according to user selections. Beware of SQL injection attacks, however. Mar 19, 2016 at 10:47
  • thank you Michael, that was helpful. I'll go read more about the dynamic SQL and come back if something wrong comes up, and thanks for the SQL inection attacks note.
    – Ciba
    Mar 19, 2016 at 11:03
  • @MichaelGreen do you know if using the bcrypt algorithm would help in dealing with the injection attacks?
    – Ciba
    Mar 22, 2016 at 17:05
  • I shouldn't think bcrypt would be much use in the general case. There are many questions here on injection - dba.stackexchange.com/search?q=injection Mar 22, 2016 at 17:57

2 Answers 2

1

Non-dynamic dynamic where clause:

create table foo (
  gender char(1),
  age smallint,
  blood_type varchar(50)
);

insert into foo values
('m', 40, 'o+'),
('m', 20, 'b-'),
('f', 30, 'b+');

set @gender = 'm';
set @age = 40;
set @blood_type = null;

select
*
from foo
where
  (@gender is null or gender = @gender)
  and (@age is null or age = @age)
  and (@blood_type is null or blood_type = @blood_type)

http://sqlfiddle.com/#!9/c9d888/3

0
  1. Build a UI with PHP or Java or VB. It will have the choices and lead to ...
  2. another page which dynamically constructs the WHERE clause plus the rest of the query, performs the query and displays the result.

This is simple and straight forward, but it does require programming knowledge in some application language. (SQL is not the place to do it.)

3
  • Thank you for your suggestion, actually we are developing the system via java eclipse. But I'm a bit curious, would constructing it that way be secure or should we apply some encryption method like the bcrypt, etc. ?
    – Ciba
    Mar 22, 2016 at 17:03
  • What needs encrypting? (Please start a separate question about encrypting things.)
    – Rick James
    Mar 22, 2016 at 17:30
  • sorry I'm a total beginner to MySQL & JAVA, and thought that encrypting the query would provide security to the system. Can you recommend me tutorials for your suggested solution for total beginners? Up till now I couldn't find one that helps explaining the basics, and I don't know if I'm searching wrong.
    – Ciba
    Mar 27, 2016 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.