Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I have to say, this strikes me as indicative of bad DB design:
    Code:
        $tbl_name = 'yourtable'; 
        $sql="INSERT INTO $tbl_name (gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp) 
            VALUES  ...
    Why does the name of the table need to be a variable? Surely you don't have more than one table with the same fields??? Or did we discuss this before?
    We've discussed this before
    Though, I do have multiple tables now. I only ever UPDATE or INSERT into the one table, though, and I still have it set as a variable simply because that's what I did in my very first PHP/MySQL script so I just continue to do it that way. It could easily be INSERT INTO opl_comp, but why should it be? Honestly, I don't see any reason to assume bad DB design based off of calling a table with a variable, or not. I see no difference in the method or result no matter how it is done, and it'd all be personal opinion in the end.

  2. #17
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I'm actually with you on this one; the $tbl_name doesn't necessarily indicate a structural fault. Especially if you want to write a system where a prefix may be added, you may choose to do something of the sorts (or assemble with {$prefix}actual_table_name for example). What IS a problem though is this: $mode = implode(",", $_POST['mode']); which is definitely a structural problem as you are putting a collection into a single field.
    'sssssssss' represents the datatype of each input variable. These are all strings. Other options are 'i' for integers, 'd' for doubles, and 'b' for binary.
    You can't use each of these directly. The problem is that mysqli_real_escape_string isn't sensitive to magic_quotes_gpc, and that mysqli_stmt is not sensitive to mysqli_real_escape_string. So lets take my data as \\mymachine\Fou-Lu's share\.
    If magic_quotes_gpc is enabled, that automatically becomes \\\\mymachine\\Fou-Lu\'s share\\. With mysqli_real_escape_string, that now becomes \\\\\\\\mymachine\\\\Fou-Lu\\\'s share\\\\. Insert this using mysqli_query and the stored result is \\\\mymachine\\Fou-Lu\'s share\\. If you insert it using prepared statements, you get \\\\\\\\mymachine\\\\Fou-Lu\\\'s share\\\\. So all of these are corrupting your data.
    With a regular query you need to escape it. But only once. With a prepared statement you do not escape it as it becomes a part of the input. The data and structure are different pieces of the puzzle, so you cannot corrupt the structure with providing it data such as I want.
    So this is why you must:
    1. Check for magic_quotes_gpc. If enabled, issue stripslashes to input (\\\\mymachine\\Fou-Lu\'s share\\ now becomes \\mymachine\Fou-Lu's share\);
    2. If you are using prepared statement, no other steps are necessary (data is still: \\mymachine\Fou-Lu's share\).
    3. If you are not using prepared statements, issue mysqli_real_escape_string to escape it (ie: data is now: \\\\mymachine\\Fou-Lu\'s share\\)

    Does that make more sense?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  3. #18
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    the $tbl_name doesn't necessarily indicate a structural fault. Especially if you want to write a system where a prefix may be added, you may choose to do something of the sorts (or assemble with {$prefix}actual_table_name for example).
    But that should only apply if you are creating something meant to be installed on many different machines. For a "one off" there's no reason to have an adjustable prefix or any other reason to have multiple table names.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  4. #19
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    But that should only apply if you are creating something meant to be installed on many different machines. For a "one off" there's no reason to have an adjustable prefix or any other reason to have multiple table names.
    For the persistence surrounding it, I have changed every instance of $tbl_name to opl_comp and removed the $tbl_name = opl_comp variable

    Quote Originally Posted by Fou-Lu View Post
    I'm actually with you on this one; the $tbl_name doesn't necessarily indicate a structural fault. Especially if you want to write a system where a prefix may be added, you may choose to do something of the sorts (or assemble with {$prefix}actual_table_name for example). What IS a problem though is this: $mode = implode(",", $_POST['mode']); which is definitely a structural problem as you are putting a collection into a single field.
    'sssssssss' represents the datatype of each input variable. These are all strings. Other options are 'i' for integers, 'd' for doubles, and 'b' for binary.
    You can't use each of these directly. The problem is that mysqli_real_escape_string isn't sensitive to magic_quotes_gpc, and that mysqli_stmt is not sensitive to mysqli_real_escape_string. So lets take my data as \\mymachine\Fou-Lu's share\.
    If magic_quotes_gpc is enabled, that automatically becomes \\\\mymachine\\Fou-Lu\'s share\\. With mysqli_real_escape_string, that now becomes \\\\\\\\mymachine\\\\Fou-Lu\\\'s share\\\\. Insert this using mysqli_query and the stored result is \\\\mymachine\\Fou-Lu\'s share\\. If you insert it using prepared statements, you get \\\\\\\\mymachine\\\\Fou-Lu\\\'s share\\\\. So all of these are corrupting your data.
    With a regular query you need to escape it. But only once. With a prepared statement you do not escape it as it becomes a part of the input. The data and structure are different pieces of the puzzle, so you cannot corrupt the structure with providing it data such as I want.
    So this is why you must:
    1. Check for magic_quotes_gpc. If enabled, issue stripslashes to input (\\\\mymachine\\Fou-Lu\'s share\\ now becomes \\mymachine\Fou-Lu's share\);
    2. If you are using prepared statement, no other steps are necessary (data is still: \\mymachine\Fou-Lu's share\).
    3. If you are not using prepared statements, issue mysqli_real_escape_string to escape it (ie: data is now: \\\\mymachine\\Fou-Lu\'s share\\)

    Does that make more sense?
    Yes it is making sense thank you for the detailed explanation. I'm now working on adjusting over to using prepared statements.
    Last edited by bemore; 03-07-2013 at 02:19 AM.

  5. #20
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    I thought we had discussed the table name stuff before, but wasn't sure.

    I have no real object to using a variable for the table name under the circumstances.

    It's just that about 90% of the time when I see that usage it means the programmer has set up multiple tables with the same structure in the mistaken belief the MySQL can 't handle more than a few dozen records per table or some other such nonsense.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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