How to insert textarea content into MySQL?

nikolai · August 22nd, 2003, 01:11 PM

Okay, is this a typo in your script or just in the message you posted?

Quote:

quote:Originally posted by a5xo3z1
..and that's mine

Code:

INSERT INTO sqlserverscript ( `id`,`category`, `description`, `explanation', `scripttext`) 
VALUES 
( '', '2', 'Skript, um eine Datenbank zu korrumpieren', NULL, 'sp_configure allow, 1 go reconfigure with override
go update sysindexes set FirstIAM = 1234
where id = OBJECT_ID(\'roysched\')go
sp_configure allow, 0 go reconfigure with override go');

If you look closely, you'll see that "explanation" is followed by a single quote, not a backtick.

That'll prevent your query from executing, though, as it's a parse error. If the row is being inserted, I don't think that you have a single-quote in the query you're trying to run.

Again, I think the easiest way for us to help you is not to describe what you think the problem is, but to post your code and let us find the problem that's really there. Too often, the two are not the same.

Take a schema dump of your table and post it along with the PHP code that generates and executes your query if you're still having problems.

Take care,

Nik
http://www.bigaction.org/

a5xo3z1 · August 25th, 2003, 12:22 AM

Hi there,

ok, I wasn't able to figure out what's wrong so here goes the whole code.

Script 1 (the Entry form)

Code:

<?php
include('is_include.php');
?>
<html>
<?php
WriteHeaderFromScript("InsideSQL :: Add Skript");
?>

    <body>

    <?php
    WriteTopFromScript();
    ?>

    <form action="f_script_entry_process.php" method="POST">
      <div align="left">
          <table>
              <tr>
                  <td>
                  Kategorie
                  </td>
                  <td>
                <input type="text" name="name_of_category" value="" size="10" maxchars="10">
                </td>
            </tr>
            <tr>
                  <td>
                  Beschreibung
                  </td>
                  <td>
                <input type="text" name="description" value="" size="100" maxchars="2000">
                </td>
            </tr>
            <tr>
                  <td>
                  Skripttext
                  </td>
                  <td>
                <textarea name ="script_text" cols="100" rows="10">
                </textarea>
                </td>
            </tr>
        </table>
        <br>
        <table>
            <tr>
                <td>
                <input type="Submit" name="Action" value="Abschicken" size="20" maxchars="20">
                </td>
                <td>
                <input type="Reset" name="Action" value="Reset" size="20" maxchars="20">
                </td>
            </tr>
        </table>
    </div>
    </form>
    </body>
</html>

Script 2 (the Process script)

Code:

<?php
include('is_db.php');

$category = $_POST['name_of_category'];
$desc = $_POST['description'];
$sc_text = addslashes($_POST['script_text']);

ConnectMySQLDB();
$sql = "INSERT INTO sqlserverscript (
    `id`,`category`, `description`, `explanation`, `scripttext`) VALUES (
     '', '$category', '$desc', NULL, '$sc_text');"; 

$result=mysql_query($sql);
/*header('Location:f_script_entry.php')*/
?>

and here's the CREATE TABLE statement from PhpMyAdmin

Code:

#
# Tabellenstruktur fÃ¼r Tabelle `sqlserverscript`
#

CREATE TABLE sqlserverscript (
  id int(3) unsigned NOT NULL auto_increment,
  category tinyint(3) unsigned default NULL,
  description varchar(255) default NULL,
  explanation text,
  scripttext text,
  PRIMARY KEY  (id),
  UNIQUE KEY IX_DESCRIPTION (description)
) TYPE=MyISAM;

Cheers,
Frank

a5xo3z1 · August 25th, 2003, 12:23 AM

Hi Nik,

Quote:

quote:Originally posted by nikolai
Okay, is this a typo in your script or just in the message you posted?

it was indeed a typo, but didnt' change anything

Cheers,
Frank

cardoso · October 2nd, 2008, 01:22 PM

Hi... did you ever figure this out? I have the exact same problem and I'm pulling my hair.

Thanks

Nelson

Old Pedant · October 2nd, 2008, 01:47 PM

It's because he had apostrophes in the data he was inserting.

He needed to *DOUBLE* each apostrophe.

He tried changing apostrophe to \' and that would be needed *IF* you had the text in the PHP code. But when doing

$sql = "INSERT INTO sqlserverfaq (
    category, dt_question, eng_question, answer) VALUES(
    '$category', '$dt_question', '$eng_question', '$ratio')"

the apostrophes inside the $xxx values are *NOT* seen by PHP. However, they *are* seen by SQL. Example:
    $sql = "INSERT INTO table ( whatever ) VALUES( '$something' )"
if the form field for $something contained
    it's a boy
then that query, *to SQL*, becomes
    INSERT INTO table ( whatever ) VALUES( 'it's a boy' )

SQL requires that you escape an embedded apostophe with a *pair* of apostrophes--NOT with \'--so you need to do a REPLACE on each apostrophe with a pair of them.

I'm not a PHP person; in Java, you'd do something like
    $something = $something.replace("\'", "''");
so do the equivalent in PHP.

It's a shame he was misled by all the bogus answers back then.