SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How's it going?


    I wondered if anyone can please tell me the full list of characters (or where I can find such a list) that cause problems with MySQL insert statements from php scripts.

    i.e. if the insert statemnt read.....

    $sql = "INSERT INTO table_name (id, data) VALUES ('" . $data . "')";


    what would cause a problem with this statement (e.g. if $data contained a ' this would cause a problem).

    Does anyone know the best way to avoid these sorts of problems with inserts and what characters cause these problems?


    Cheers,

    Jason

  2. #2
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use the function addslashes() on the data items you want to insert into the database e.g.

    PHP Code:
    $sql "INSERT INTO table_name (id, data) VALUES ('" addslashes($data) . "')"
    Then when you read the information back from the database use: stripslashes()
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  3. #3
    SitePoint Wizard
    Join Date
    Apr 2000
    Posts
    1,483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I remember correctly the problem characters are \ " and '

    But as Karl said, the best way of avoding the problem completely is to use addslashes().

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys - but solution doesn't seem to be quite as simple as that.

    To illustrate, take the extreme example:

    $data = '<a href='http://www.whatever.com/something/file.php?time=60389342&loc=109'target="_blank<span class="class_type"><b>prince </b></span></a>';

    i.e. that we wanted to insert a full link into a table.....

    ...adding slashes to $data before the insert still doesn't seem to insert the contents of $data into the table.


    I'm looking for something generic that can be applied whatever the contents of a string.. any further ideas?


    Cheers again,

    Jason

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Washington State
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This looks like a string problem beforehand. Try :

    echo $data;

    And see if it works properly, it shouldn't. You have this to start it off :

    $data = '<a href='http

    The second ' kills it, it should give you a parse error. One can put " in ' ' and ' in " " so try something like this :

    $data = '<a href="http or
    $data = "<a href='http or
    $data = '<a href=\'http or
    $data = "<a href=\"http or

    or to expand :

    $data = '<span class="class_type"><b><a href="http://www.whatever.com/file.php?time=33&loc=11" target="_blank">prince</a></b></span>';

    Not sure the validity of that html but check out this very useful tutorial on strings :

    http://www.zend.com/zend/tut/using-strings.php

  6. #6
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Philip,

    Good to see someone using the correct string usage:

    i.e. using ' ' instead of " " when there is no variable substitution to be done in the string.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Philip - I'll have a read.


    Further investigation into the problem reveals that there is never a problem with the inserts from the php script after at least 1 row has been 'manually' inserted into the table (i.e. in php_myAdmin / telnet).

    Has anyone encountered this before please?

    Cheers...

    Jason

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Washington State
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Karl. Yes, improper use of quotes is a pet peeve of mine, as is improper use of printf. For instance, this makes me cringe :

    $var = "some plain old words";

    Ack! Plain text need not parse, it's just plain text! Wasting valuable php resources likes this (however subtle) can easily be avoided with a little teaching. But, what's "preferred" between the following :

    $var = "some $animal likes food";
    $var = 'some '. $animal .' likes food';

    I prefer the second, some prefer the first. Concatenating strings can be fun! Also, the second is slightly faster. Now, this is not appropriate :

    $var = 'a '. $a .' b '. $b .' c '. $c .' d '. $d;

    But I digress. Oh, regarding printf ... people, please do NOT use printf unless you know EXACTLY why you're using it. Use print of echo instead. ;-)

  9. #9
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Washington State
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jason,

    You may want to play with mysql_error() function :

    http://www.php.net/manual/en/function.mysql-error.php

    It's quite useful. I don't understand your issue here but mysql_error outputs the last mysql error. A popular way to use it is as such :

    $result = mysql_query( ... ) or die(mysql_error());

    Which if an error exists, it'll output it and terminate the script. Btw, mysql_error converts the error into "human readable" format so us humans can understand them.
    Last edited by Philip Olson; Mar 7, 2001 at 17:13.

  10. #10
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So Philip can you tell us exactly how much more system intensive it is to use printf() instead of print or echo, I use it when I need to do variable substitution in a string, I use a lot also. Please clarify with examples or benchmarks if you can as to why it is that much worse.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Washington State
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Freddy,

    Personal preferences also come to play, some just find it easier to work with. At any rate, Sterling Hughes mentions it in this article titled "Top 21 PHP progamming mistakes - Part I: Seven Textbook Mistakes" :

    http://zend.com/zend/art/mistake.php#Heading4

    Some useful words in there about this topic, should be of interest.

  12. #12
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have read that one, people have been always telling me that I should not use printf() or sprintf() for anything except formatting strings and the like. There was some talk awhile back about printf() taking more resources than print() or echo(), personally I have only been doing PHP for a little over 1 year, but I have never had a performance problem when using printf() vs print(). It could be that it really is more a personal preference more than anything, and possibly since I don't have millions of visitors at my site everyday either.

    But it does not seem like this

    PHP Code:
    $str "the blue "$animal ." eats food"
    as opposed to this would be any less system intensive

    PHP Code:
    $str sprintf('the blue %s eats food'$animal); 
    It would appear that both would need to do variable substituion, whether or not there is a performance issue here, I don't know but the second method is much cleaner for me personally.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  13. #13
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Washington State
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd do :

    $str = 'the blue '. $animal .' eats food';

    Vs.
    $str = "the blue $animal eats food"; or
    $str = "the blue ". $animal ." eats food"; or
    $str = sprintf('the blue %s eats food', $animal);

    Using sprintf in this case will always be slower. A function is being called when there's no need to call it here as $animal is already a string and isn't being adjusted.

    The f functions are more complicated then let's say plain old print or echo. There's much more involved here then just variable substitution, they do formatting when many times this formatting is not necessary. sprintf works nicely while formatting numbers, it as well as number_format() are very useful. I wish I knew more specifics on this and will research it a bit more in the coming weeks, and ask around.

  14. #14
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with you in that the f() function serve a much greater purpose than variable substitution such as number padding and decimal formatting.

    I just can't see the huge problem with using it for only variable substitution. Show me the benchmarks that show using the f() function are that slow and I might change my outlook but I have gotten into the habit of using them solely for var substitution, and I have yet to see proof that I shouldn't be.

    I too shall do some research. Thanks for the input.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  15. #15
    SitePoint Zealot moshe_be's Avatar
    Join Date
    Dec 2000
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have had problem with the # character that I did not solve, It can't seem to pass with \# or anything.

    The # character used for comments and if I got it on the URL to jump into anchors it does not pass the query

    Anyone encountered that problem?

  16. #16
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Damn it - this is very annoying....

    in phpMyAdmin when I try to insert one of the 'offending' rows, it accepts the row into the table but without the usual confirmation message ('thank you your SQL request has been executed successfully....e.t.c.).

    Yet the same row cannot be inserted from the php script, and there's no error message - just nothing.

    Does this shed any futher light?

    Cheers once again,

    comin at ya!!!!!!!!! (sorry madness caused by MySQL)

    Jason

  17. #17
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Washington State
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exactly what SQL statement are you trying? And when it does find its way into the database, does the entire value make it or part of it? Do very simple statements work? Also, what is your magic_quotes_gpc setting? (see phpinfo() for this setting info).

  18. #18
    SitePoint Zealot moshe_be's Avatar
    Join Date
    Dec 2000
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In phpMyAdmin if you have only one insert you don't get query was executed successfully.

    If you got two or three with ; between them you will get that message.

  19. #19
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys - I'll have a further look into it.


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
  •