SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Query from PHP code

    Good day,

    We are developing a commercial website using PHP and MySQL. We have different kind of customers, and we have created tables for each kind.

    We have also developed an administrative area for reviewing the purchases and customers information.
    We select the kind of customers we want to check in the first page of the administrative area.

    Here some examples of tables and variables involved:
    - In the first page the kind of customer is selected from a combo box, and passed to the next page as a post variable
    - Customers tables have names like:
    Table Customers_Kind1
    id
    Customer_Name
    ...

    So, depending which customer is selected in the first page, the query will involve a different table, for instance:
    $SQL = "SELECT Customer_Name FROM Customers_Kind1"
    $SQL = "SELECT Customer_Name FROM Customers_Kind2"

    In order to have just one generic query to the database, could you please let me know the correct syntaxis to concatenate strings using PHP, and use the post variable with the selected customer to create the SQL query?
    Something like this:
    $SQL = "SELECT Customer_Name FROM Customers_".$POST['selected_customer']

    Thanks a lot!!

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    First off, it is $_POST['selected_customer'], second, that is a SQL Injection waiting to happen. Be sure to use mysql_real_escape_string on it or use a prepared statement or mysqli functions.

    example:
    PHP Code:
    $SQL "SELECT Customer_Name FROM Customers_".mysql_real_escape_string($_POST['selected_customer']); 
    Lastly, just testing your code would have indicated if you did it right or not and would have identified you were referencing your POST data incorrectly.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Sir_Arcturua View Post
    $SQL = "SELECT Customer_Name FROM Customers_Kind1"
    $SQL = "SELECT Customer_Name FROM Customers_Kind2"
    Thanks a lot!!
    Proper design should be a single table with a column indicating Cust. Type

    Code:
    $SQL = "SELECT CUSTOMER_NAME FROM CUSTOMERS WHERE CUSTOMER_TYPE = '" . mysql_real_escape_string($_POST['selected_customer']) . "'"

  4. #4
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi cpradio,

    Thanks a lot for your answer.
    Could you please tell me more about function mysql_real_escape_string?
    Why not just add the $_POST variable?

    Also, in case I am using a $_SESSION variable, as I have to use it in MySQL queries in several pages, is it the same format?
    I mean:
    $SQL = "SELECT Customer_Name FROM Customers_".mysql_real_escape_string($_SESSION['selected_customer']);

    Thanks a lot!!!!

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    By using $_POST['selected_customer'] directly, you are setting yourself up for a SQL Injection (google it for more information).

    By using mysql_real_escape_string() you are preventing a SQL Injection from happening.

    As for, do you need to use it with your $_SESSION variable? Maybe, it depends on how you stored the $_POST data into session.

    If you did (I recommend this method)
    PHP Code:
    $_SESSION['selected_customer'] = $_POST['selected_customer']; 
    Then you definitely need to use mysql_real_escape_string()

    If you did (I actually do not recommend this method, as you really should store the posted data as is, so displaying it back to the user won't be affected)
    PHP Code:
    $_SESSION['selected_customer'] = mysql_real_escape_string($_POST['selected_customer']); 
    Then you do not necessarily need to use mysql_real_escape_string again.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  6. #6
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi cpradio,

    Thanks a lot for your answer.
    Reading about SQL Injection I understood about the function mysql_real_escape_string(). I'm clear now I have to use it.

    This is what I am thinking to do:
    On page 1, I have a dropbox for selecting the customer.
    Depending the selection, I know which custimer I have to use, so which suffix I have to add to the SQL query.
    I put this suffix in a session variable, as I will need it in several pages to use it to build SQL queries.
    In each page, I can create a local variable for being use in this page, for instance:
    $_SESSION['selected_customer'] = $customer;

    And the use it for the SQL query:
    $SQL = "SELECT Customer_Name FROM Customers_".mysql_real_escape_string($customer);

    Is it ok?

    Thanks a lot!!!

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Yes, except you have the local variable assignment backwards.
    PHP Code:
    $customer $_SESSION['selected_customer']; 
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2012
    Location
    United Kingdom
    Posts
    78
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I'd just like to point out that using mysql_real_escape_string() will not prevent SQL injection attacks because there are no single quotes encasing its value. This means that we can still manipulate the query to whatever we'd like:
    PHP Code:
    $evil ' WHERE id = 1';

    $query mysql_query('SELECT * FROM table'.mysql_real_escape_string($evil)); 
    It's just as well MySQL's PHP interface does not support stacked queries (or as far as I know, at least), otherwise the attacker could wreak havoc with your tables:
    PHP Code:
    $evil '; DROP TABLE table--';

    $query mysql_query('SELECT * FROM table'.mysql_real_escape_string($evil)); 
    I'd personally do what K.wolf stated above, have another column stating the type of customer they are. However if you do want to do it this way, then validate the HTTP POST data with a pre-set list of known table names:
    PHP Code:
    $valid = array('A1''B2''C3');
    if(!
    in_array($_POST['key'], $validTRUE))
    {
        die;


  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Proper design should be a single table with a column indicating Cust. Type

    Code:
    $SQL = "SELECT CUSTOMER_NAME FROM CUSTOMERS WHERE CUSTOMER_TYPE = '" . mysql_real_escape_string($_POST['selected_customer']) . "'"
    Use the following if you are going to go with a table per customer setup to prevent the SQL Injection
    PHP Code:
    $SQL "SELECT Customer_Name FROM `Customers_".mysql_real_escape_string($_POST['selected_customer']).'`'
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2012
    Location
    United Kingdom
    Posts
    78
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Use the following if you are going to go with a table per customer setup to prevent the SQL Injection
    PHP Code:
    $SQL "SELECT Customer_Name FROM `Customers_".mysql_real_escape_string($_POST['selected_customer']).'`'
    Grave accents aren't escaped by MRES, only quotes (single & double) and backslashes; so it's still vulnerable to SQL injection:
    PHP Code:
    $evil '` WHERE id = 1#';

    $query mysql_query('SELECT * FROM `table'.mysql_real_escape_string($evil).'`'); #selects where column ID is equal to 1 

  11. #11
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by modernW View Post
    Grave accents aren't escaped by MRES, only quotes (single & double) and backslashes; so it's still vulnerable to SQL injection:
    PHP Code:
    $evil '` WHERE id = 1#';

    $query mysql_query('SELECT * FROM `table'.mysql_real_escape_string($evil).'`'); #selects where column ID is equal to 1 
    Okay, tie it to proper validation of the $_POST data and he should be good. @Sir_Arcturua ;, be sure to verify the selected_customer is valid and contains only valid characters (a-z0-9) before using it.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •