Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

edit:

I can replace phone, -10 (that is last 10 digits of phone ) with full phone digits.

on tip i got is SELECT….FROM… WHERE phone IN (…) UNION SELECT … FROM … WHERE email IN (…)

edit end

I am working with a premade sql code in a Java based application. Database used is Mysql.

It somehow does not seems a good optimized query to me.

  1. Are there better ways to do what is happening here?
  2. Secondly, I dont understand how the list of strings/numbers could possibly be injected. Can someone explain this too?
  3. The time for the query to execute is very long. I have index on phone and email. still index not used. Any optimization ?

Query in java:

SELECT id, phone FROM application_user WHERE ('"+contactsList+"' REGEXP SUBSTR(phone, -10) OR '"+contactsList+"' REGEXP email ) "

becomes this in sql logs

SELECT id, SUBSTR(phone, -10) phone FROM application_user WHERE ('[3134548664, 117, 114,
 3224572751, 115,3455353536, 3007272543, 3458642248, 1551862974, 3454363328, 1552917746, 
3334465201, 3334671958, 300,3134763130, 7707691854, 200, 3004420566, 3134955672, 3454363328, 
3144619313, 3244630158, 3134086188, 3457078244, 1551374023, 3017661600, 3008015105, 3224666670, 
3134955672, 3336306010, 3344669753, 15, 16, 310, 1563010346, 3216924067, 3337236903, 
3344177260, 1111225563, 3035000129, 3456883428, 101, 100, 222, 3008472445, 3017661600, 
3127013007, 3006163335, 3447226682, 3214054181, 3455353026, 1217, 3455807333]' 
REGEXP SUBSTR(phone, -10) 
    OR 
    '[3134548664, 117, 114, 3224572751, 115, 3455353536, 3007272543, 3458642248, 
1551862974,3454363328, 1552917746,3334465201, 3334671958, 300, 3134763130, 7707691854, 
200,3004420566, 3134955672, 3454363328, 3144619313, 3244630158, 3134086188, 3457078244, 
1551374023, 3017661600, 3008015105, 3224666670, 3134955672, 3336306010, 3344669753, 15, 16, 
310, 1563010346, 3216924067, 3337236903, 3344177260, 1111225563, 3035000129, 3456883428, 101, 
100, 222, 3008472445, 3017661600, 3127013007, 3006163335, 3447226682, 3214054181, 3455353026, 
1217, 3455807333]' 
REGEXP email );

time

and takes Query_time: 29.401041  

rows in that table:

20,000,000 , phone is int type. I can conver it to varchar if its a solution.

java class for list

public class Contact{

    private long id;
    private int applicationUserId;
    private int typeId;
    private String typeString;
    private String contactName;
    private String contactNumber;

    private String createdDatetime;
share|improve this question
    
To answer the question of where the phone numbers are coming from, it is because you have dynamic SQL that is being created in the Java code. The Java variable contactsList is being concatenated into the query text which is ripe for sql injection depending on where that variable is created. I have not worked with MySql that much for the REGEX functionality but it looks like on the server side there are REGEXs for valid Phone numbers and Emails. Moving those to the Java code may help things but with out knowing more about the APP it would be premature. –  Wind Raven Jan 21 at 22:36
    
thanks ........ –  Mab Jan 23 at 21:48
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.