#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0

    Help with SQL Statement


    I need to know how to make the following statement work.
    Here is the statement:
    PHP Code:
    $colname_test "-1";
    if (isset(
    $_GET['Vendor'])) {
      
    $colname_test $_GET['Vendor'];
    }
    $colname1_test "-1";
    if (isset(
    $_GET['Equipment'])) {
      
    $colname1_test $_GET['Equipment'];
    }

    mysql_select_db($database_TechSupport$TechSupport);
    $query_test1 sprintf("SELECT MaillingList.Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email FROM MaillingList WHERE MaillingList.Switch IN (SELECT MaillingList.Switch FROM MaillingList WHERE MaillingList.Vendor = %s AND( MaillingList.Postion = 'DR' OR MaillingList.Postion = 'MS' OR MaillingList.Postion = 'MC' OR MaillingList.Postion = 'ST') AND  'Y' = %s) GROUP BY MaillingList.Switch"GetSQLValueString($colname_test"text"),GetSQLValueString($colname1_test"text"));
    $test1 mysql_query($query_test1$TechSupport) or die(mysql_error());
    $totalRows_tes1t mysql_num_rows($test1); 
    Where i am having issues is the 'Y' = %s in the code. The %s is a Field in my SQL database. It is putting quotations around it. How do i get take them out so that way the sql statement treats it like a field in the database.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    Here is the echo of the statement

    SELECT MaillingList.Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email FROM MaillingList WHERE MaillingList.Switch IN (SELECT MaillingList.Switch FROM MaillingList WHERE MaillingList.Vendor = 'Lucent' AND( MaillingList.Postion = 'DR' OR MaillingList.Postion = 'MS' OR MaillingList.Postion = 'MC' OR MaillingList.Postion = 'ST') AND 'Y' = 'BWM') GROUP BY MaillingList.Switch

    What i market in red is where the issue is.
    BWM is a field in my database and because of the quotes it doesn't work.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    170
    Rep Power
    55
    Try using backticks
    PHP Code:
    `Y` = `". %s ."
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    When i do it that way I get a error

    syntax error , unexpected '%'

    any other ideas
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    No idea what SecurityDavid was trying to do, but this doesn't work.

    However, he's right about the backticks. You need to take the raw value(!), escape special characters and then wrap it in backticks. When you use your escape function, the value will always be wrapped in quotes -- which is not what you want.

    But I find the whole idea rather odd and dangerous. You let the user choose any column he wants. That's a bad idea and points to a misdesign of either the application or the database.

    And you should get used to indenting your queries. Those one-liners are completely unreadable and very error-prone.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    So how would i do this.
    I have a data base with email address in it.
    the database has 15 fields
    the user has 2 options
    1st option is a variable in field 1
    2nd option is the field that is looking for Y

    SO with the data that i have on my first post how would i do this
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Is that a convoluted version of this:
    Code:
    SELECT Switch
         , GROUP_CONCAT(emailaddress) Email 
      FROM MaillingList
     WHERE vendor = 'Lucent'
       AND postion IN('DR','MS','MC','ST') 
       AND BWM = 'y'
     GROUP 
        BY switch
    ?

    Incidentally, not that it matters, but neither 'mailling' nor 'postion' have any meaning in English, although I note that they are similar in spelling to the recognised English words 'mailing' and 'position'.
    Last edited by cafelatte; September 25th, 2012 at 08:30 AM.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    SELECT Switch,
    GROUP_CONCAT(MaillingList.Emailaddress) as Email
    FROM MaillingList

    WHERE Switch IN (SELECT Switch
    FROM MaillingList

    WHERE Vendor = 'Lucent'
    AND(Postion = 'DR' ORPostion = 'MS' OR Postion = 'MC' OR Postion = 'ST')
    )
    AND 'Y' = 'BWM'
    GROUP
    BY Switch

    This is my sql statement. The BWM is a column name in my database that i am passing from another web page. The issue is that it has the single qoutes around it. I need to remove the qoutes so the sql query will work. How can i do this
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    The Code looks like this

    PHP Code:
    $colname_test "-1";
    if (isset(
    $_GET['Vendor'])) {
     
    $colname_test $_GET['Vendor'];
    }
    $colname1_test "-1";
    if (isset(
    $_GET['Equipment'])) {
      
    $colname1_test $_GET['Equipment'];
    }

    mysql_select_db($database_TechSupport$TechSupport);
    $query_test1 sprintf("
    SELECT MaillingList.Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email 
    FROM MaillingList
    WHERE MaillingList.Switch IN 
    (SELECT MaillingList.Switch 
    FROM MaillingList 
    WHERE MaillingList.Vendor = %s(this is colname_test) 
    AND( MaillingList.Postion = 'DR' OR MaillingList.Postion = 'MS' OR MaillingList.Postion = 'MC' OR MaillingList.Postion = 'ST') )
    AND  'Y' = %s ( THis is colname1_test)
    GROUP BY MaillingList.Switch"

    GetSQLValueString($colname_test"text"),GetSQLValueString($colname1_test"text"));
    $test1 mysql_query($query_test1$TechSupport) or die(mysql_error());
    $totalRows_tes1t mysql_num_rows($test1); 
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by rwtrwt2003
    The BWM is a column name in my database that i am passing from another web page. The issue is that it has the single qoutes around it. I need to remove the qoutes so the sql query will work. How can i do this
    Haven't I already answered this question?

    Originally Posted by Jacques1
    You need to take the raw value(!), escape special characters and then wrap it in backticks.
    "Escaping" in this context means: apply mysql_real_escape_string or an equivalent function and delete all backticks (since they aren't allowed within identifiers at all).
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    170
    Rep Power
    55
    Originally Posted by Jacques1
    No idea what SecurityDavid was trying to do, but this doesn't work.
    Sorry, I'm not really familiar with the use of the percent sign here. The answer was to make sure it was enclosed in backticks, which is correct; but I saw the %s as a variable, which I'm assuming is not correct. Can someone explain to me exactly what the % is used for here?
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0

    Talking


    I got it to work,
    This is the statement that i used
    PHP Code:
    $colname_test "-1";
    if (isset(
    $_GET['Vendor'])) {
      
    $colname_test $_GET['Vendor'];
    }
    $colname1_test "-1";
    if (isset(
    $_GET['Equipment'])) {
      
    $colname1_test $_GET['Equipment'];
    }
    $colname1_test mysql_real_escape_string($colname1_test);
    mysql_select_db($database_TechSupport$TechSupport);
    $query_test1 sprintf("SELECT MaillingList.Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email FROM MaillingList WHERE MaillingList.Switch IN (SELECT MaillingList.Switch FROM MaillingList WHERE MaillingList.Vendor = %s AND( MaillingList.Postion = 'DR' OR MaillingList.Postion = 'MS' OR MaillingList.Postion = 'MC' OR MaillingList.Postion = 'ST') AND  'Y' = $colname1_test) GROUP BY MaillingList.Switch"GetSQLValueString($colname_test"text"),GetSQLValueString($colname1_test"text"));
    $test1 mysql_query($query_test1$TechSupport) or die(mysql_error());
    $totalRows_tes1t mysql_num_rows($test1); 
    Thank you for all your Help
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    this is fixed

IMN logo majestic logo threadwatch logo seochat tools logo