Professional Web Applications Themes

how to prevent inputting quotation mark (')? - MySQL

Hi, I made a form where visitors can introduce data. I use this: strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" & dat & ...)" My problem is that when someone introduces a quotation mark, i get a error and the insert fails (e.g. nam'e). How can i prevent that? Controling each entered character seems me to be a very big work ......

  1. #1

    Default how to prevent inputting quotation mark (')?

    Hi,

    I made a form where visitors can introduce data.
    I use this:
    strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
    dat & ...)"

    My problem is that when someone introduces a quotation mark, i get a error
    and the insert fails (e.g. nam'e).

    How can i prevent that? Controling each entered character seems me to be a
    very big work ...


    Pat Guest

  2. #2

    Default Re: how to prevent inputting quotation mark (')?

    > I made a form where visitors can introduce data.
    > I use this:
    > strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
    > dat & ...)"
    >
    > My problem is that when someone introduces a quotation mark, i get a error
    > and the insert fails (e.g. nam'e).
    >
    > How can i prevent that?
    Use prepared statements.
    >Controling each entered character seems me to be a
    > very big work ...
    I believe MySQL has a function for that in the API ...
    mysql_escape_string or something.


    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
    Server
    Upscene Productions
    [url]http://www.upscene.com[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

  3. #3

    Default Re: how to prevent inputting quotation mark (')?

    Can't you use the replace function (replacing the quotation mark with
    an empty string).

    strsql = "INSERT INTO mytable (field1) values (replace(" & lol & ",
    char(39), ''))"

    Mike Guest

  4. #4

    Default Re: how to prevent inputting quotation mark (')?

    > Can't you use the replace function (replacing the quotation mark with
    > an empty string).
    >
    > strsql = "INSERT INTO mytable (field1) values (replace(" & lol & ",
    > char(39), ''))"
    Well, although that might solve the quote problem, it does change
    the data being saved.

    For example, if this was an application that keeps user accounts
    and my name was O'Malley -- I would not be amused if it saved
    my name as O Malley...

    If the user want's to input a ' and it makes sense, then allow the
    user to do so. You shouldn't modify data before it is inserted
    into the database if the data is valid in the given domain. (eg:
    ` is not valid in numbers, obviously )



    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
    Server
    Upscene Productions
    [url]http://www.upscene.com[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]



    Martijn Tonies Guest

  5. #5

    Default Re: how to prevent inputting quotation mark (')?

    "Pat" <wfdvbfdfg.cv> wrote in message
    news:W7SdnbCqr7ggTkXeRVnyggscarlet.biz...
    > I made a form where visitors can introduce data.
    > I use this:
    > strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
    > dat & ...)"
    >
    > My problem is that when someone introduces a quotation mark, i get a error
    > and the insert fails (e.g. nam'e).
    Quote characters can be escaped so that they can be used in SQL strings by
    using the backslash (\) character. E.g. INSERT INTO mytable VALUES
    ('O\'Malley')
    Also these characters can be encoded according to standard SQL as two quote
    characters:
    INSERT INTO mytable VALUES ('O''Malley')
    See [url]http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html[/url].

    Instead of filtering the string yourself, Perl and PHP offer API functions
    to "encode" strings to be friendly to SQL syntax. In Perl DBI the function
    is $dbh->quote($value), and in PHP it's mysql_escape_string().

    See these pages for more information:
    [url]http://www.oreilly.com/catalog/perldbi/chapter/ch04.html[/url] (for Perl DBI)
    [url]http://us3.php.net/mysql_escape_string[/url] (for PHP)

    The other method (which Martijn mentioned) is to send string values as
    parameters to a prepared statement. See this article for more information:
    [url]http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html[/url]
    When sending strings as parameters, you don't need to escape special
    characters.

    Regards,
    Bill K.


    Bill Karwin Guest

  6. #6

    Default Re: how to prevent inputting quotation mark (')?

    Pat wrote:
    > Hi,
    >
    > I made a form where visitors can introduce data.
    > I use this:
    > strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
    > dat & ...)"
    >
    > My problem is that when someone introduces a quotation mark, i get a error
    > and the insert fails (e.g. nam'e).
    >
    > How can i prevent that? Controling each entered character seems me to be a
    > very big work ...
    $field1=eregi_replace("[^-a-z0-9.() ,]*","",$field1);
    is one way that works. That will remove any character except an
    alphanumeric, dash (-), period (.), parenthesis (()), space ( ), or
    comma (,). Adjust as you see fit.

    See also stripslashes().

    HTH

    --
    TK
    [url]http://www.wejuggle2.com/[/url]
    Still Having a Ball


    A: Because it messes up the order in which people normally read text.
    Q: Why is it such a bad thing?
    A: Top-posting.
    Q: What is the most annoying thing on usenet?






    ..

    ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
    [url]http://www.newsfeeds.com[/url] The #1 Newsgroup Service in the World! 120,000+ Newsgroups
    ----= East and West-Coast Server Farms - Total Privacy via Encryption =----
    Terry Guest

  7. #7

    Default Re: how to prevent inputting quotation mark (')?


    "Martijn Tonies" <m.toniesupscene.removethis.com> wrote in message
    news:11tid05f5sgjd8acorp.supernews.com...
    >> Can't you use the replace function (replacing the quotation mark with
    >> an empty string).
    >>
    >> strsql = "INSERT INTO mytable (field1) values (replace(" & lol & ",
    >> char(39), ''))"
    >
    > Well, although that might solve the quote problem, it does change
    > the data being saved.
    >
    > For example, if this was an application that keeps user accounts
    > and my name was O'Malley -- I would not be amused if it saved
    > my name as O Malley...
    mysql_escape_string() and mysql_real_escape_string() saves the ' and " but
    escapes them with a preceding \
    BUT it doesn't handle ; so you will have to strip that one out yourself.
    all these are used with SQL INJECTION, something you don't want, so I
    suggest you escape your incoming data string variable.
    >
    > If the user want's to input a ' and it makes sense, then allow the
    > user to do so. You shouldn't modify data before it is inserted
    > into the database if the data is valid in the given domain. (eg:
    > ` is not valid in numbers, obviously )
    >
    >
    >
    > --
    > Martijn Tonies
    > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
    > Server
    > Upscene Productions
    > [url]http://www.upscene.com[/url]
    > Database development questions? Check the forum!
    > [url]http://www.databasedevelopmentforum.com[/url]
    >
    >
    >

    Jim Michaels Guest

  8. #8

    Default Re: how to prevent inputting quotation mark (')?

    It saves it fine, but then on retrieval or display, php displays the "" as quetion marks(?) any help on display
    Unregistered Guest

Similar Threads

  1. Replies: 6
    Last Post: November 13th, 12:06 PM
  2. Inputting Chinese Fonts
    By Lim_Meng_Yew@adobeforums.com in forum Adobe Illustrator Windows
    Replies: 0
    Last Post: April 17th, 07:38 PM
  3. how can I store a quotation mark as text?
    By Christian Grass in forum Macromedia Director Lingo
    Replies: 3
    Last Post: November 25th, 07:04 PM
  4. Replies: 8
    Last Post: October 3rd, 01:10 PM
  5. Replies: 3
    Last Post: July 9th, 12:41 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139