SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query works except when variable has apostrophe in it

    Hello,

    The query below works if $find is just a regular word with no special characters, and it even works if $find has a "%" in it or a "&".

    However, it does not work if $find has an apostrophe in it. Any ideas on how I can change the code to make it work if $find has an apostrophe in it?

    Thanks in advance,

    John

    Code:
    <?
     
    $find1 = urlencode($find); 
    print     "<form action='process.php?find=$find1' method='post'>
            Add site: <input name='site' type='text' size='50'>
            <input type='submit' value='Submit'>
            </form> ";
    ?>
    Then, on process.php, I have:
    Code:
    <?
     
    $remove_array = array('http://www.', 'http://', 'www.');
    $site = str_replace($remove_array, "", $_POST['site']);
    mysql_connect("mysqlv10", "username", "password") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
     
    $_GET['find'] = $find;
    $_GET['find'] = stripslashes($_GET['find']);
    $find = urldecode($find);
     
    mysql_query("INSERT INTO `$find` VALUES (NULL, '$site',1,0)");
     
     
    ?>

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Why are you taking a table name as form input???

    This smells like supremely bad DB design

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I added $site = mysql_real_escape_string($site); and it didn't solve the problem.

    Right now I'm not worried about security. I just want to get it to work first, and it does except when $find has an apostrophe in it.

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you're allowing apostrophes in table names, then you must've already figured out how to handle it when you ran the CREATE TABLE queries for those names... right?

    I have to ask, because I suspect you don't mean to have variably named tables.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    I have to ask, because I suspect you don't mean to have variably named tables.
    actually, that's exactly what he wants

    http://www.sitepoint.com/forums/showthread.php?t=620714
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah. It's what I want.

    I used phpMyAdmin to make a table called "john's favorites".

    My site pulls it up, but then I can't apply the above code to it.

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You should take the advice in that other thread, helping you with this is helping you do the wrong thing.

    Tables are containers for sets of data, the table itself is not a piece of data.

  8. #8
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, it almost works except for apostrophes, slashes and periods. I was thinking of just banning slashes and periods.

    Maybe for version 2.0 of my app I will switch to a different database configuration but for now I want to roll with one table per topic.

    So I guess I would appreciate it if you could help me do the "wrong" thing for version 1.0.


  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    look, there are two ways you can take it from here -- listen to our advice and don't do one table per topic, or ignore our warnings and do it

    pick one

    but do ~not~ ask us to help you with the wrong approach

    besides, doesn't the fact that you're ~already~ stuck (declaring table names with special characters) give you any sort of hint?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Instead of tables called
    john's favorites
    dave's favorites
    helen's favorites

    have one table called favorites and an owner column (which would store john, dave, helen etc.)
    Then your query would be something like:
    Code SQL:
    SELECT * FROM favorites WHERE owner = '$owner';
    -- after escaping $owner

    I agree with the others that your original design is too wrong to be worth fixing.


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
  •