Create table query

Question

I want to create a table for making a comment box. I was told that I should be wary of sql injection (dont even know what that means).

So I thought I should ask around at SO. my requirements are:

Comments table

a comment row ~400 chars
aid -> every comment should be linked to an aid. duplicates should be allowed. means aid = 21, can have more than 1 comment. I should be able to search through the DB to see all the comments related to aid = 21.
timestamp for the comment
userid for the comment.

A MySQL query for the above table that should not allow SQL injection. I am pretty confused. any help would be highly appreciated. thanks a lot in advance.

Victor Nicollet · Accepted Answer · 2009-12-27 20:08:55Z

3

Creating a table usually happens only once, when the system is installed. There is, therefore, no risk of SQL injection (which happens when a query is run with data provided by the user).

The above description would probably be implemented as:

CREATE TABLE `comment` ( 
  `comment_id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `comment_text` VARCHAR(400) NOT NULL,
  `aid_id` INTEGER NOT NULL REFERENCES `aid`(`aid_id`),
  `comment_time` DATETIME NOT NULL,
  `user_id` INTEGER NOT NULL REFERENCES `user`(`user_id`)
);

answered Dec 27, 2009 at 20:08

Victor Nicollet

24.7k4 gold badges62 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

amit Over a year ago

it is not printing the timestamp. ie, the current time. its printing 0000-0000....also the comment_text is not updating. its null.

Glenn Over a year ago

It is generally good practice not to use SQL reserved words as identifiers (i.e. table or column names). Although the word "comment" is not in the SQL standard ( see contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ), it is a reserved word for DB2 ( see table 2 from publib.boulder.ibm.com/infocenter/db2e/v8r2/index.jsp?topic=/… )

Victor Nicollet Over a year ago

A table creation query is intended to create a table, not to print anything. You might want to ask a question related to your queries for reading the data.

John G · Accepted Answer · 2009-12-27 20:06:48Z

1

Try and use stored procedures in mysql .

Use parameters to pass the input to the stored procedure.

answered Dec 27, 2009 at 20:06

John G

2,1862 gold badges11 silver badges5 bronze badges

Comments

streetparade · Accepted Answer · 2009-12-27 20:08:01Z

0

thuis tutorial is for you . http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

answered Dec 27, 2009 at 20:08

streetparade

33.1k39 gold badges106 silver badges123 bronze badges

Comments

user213154 · Accepted Answer · 2009-12-27 20:11:59Z

0

SQL injection is explained at Wikipedia and other places.

Use mysql_real_escape_string() or stored procedures are standard techniques that will avoid SQL injection.

answered Dec 27, 2009 at 20:11

user213154

Collectives™ on Stack Overflow

Create table query

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related