Whats wrong with this INSERT query?

Question

Heres my create query, works just fine...

$pages = "CREATE TABLE pages (id int NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHAR(255), content TEXT, description VARCHAR(255), banner VARCHAR(255), headercol INT, headerdisplay INT, type INT, headersort INT, category INT, footercol INT, footerdisplay INT, footersort INT)";
mysqli_query($dbc, $pages) or die('Error querying database5');

Heres my insert query, returns error...

$jewelry = "INSERT INTO pages (name, description, banner, headerdisplay, type, headersort, category) VALUES ('Jewelry', 'A collection of vintage and contemporary jewelry', 'uploads/banners/jewelry.png', 1, 2, 1, 0)";
mysqli_query($dbc, $jewelry) or die('Error inserting jewelry');

By the way, i didnt get any error.

Angelin Nadar
– Angelin Nadar

2012-10-12 07:10:59 +00:00
Commented Oct 12, 2012 at 7:10 — Angelin Nadar
– Angelin Nadar, Commented Oct 12, 2012 at 7:10

KARASZI István · Accepted Answer · 2012-10-12 07:11:54Z

3

You have an unescaped apostrophe at the second column value for description.

You should escape it with backslash, like this: \'.

Update

If you want to insert from a variable, then you should create a prepared statement with mysqli_prepare and bind parameters with mysqli_stmt_bind_param.

Example

OOP way:

$stmt = $mysqli->prepare("INSERT INTO pages (name, description, banner, headerdisplay, type, headersort, category) VALUES (?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param($name, $description, $banner, $headerDisplay, $type, $headerSort, $category);

Or you could use PDO or other database abstraction layer.

edited Oct 12, 2012 at 7:11

answered Oct 12, 2012 at 7:05

KARASZI István

31.5k9 gold badges106 silver badges133 bronze badges

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

2 Comments

user1621945 Over a year ago

damn skippy... so how do I escape this, and would this be an issue if it was inserting $description?

Elias Van Ootegem Over a year ago

@user1621945: using prepared statements will escape all chars that need escaping for you, that's why the update mentions mysqli_prepare. BTW: I don't know what exactly you're planning to store in that DB, but using TEXT fields should be avoided if possible (each query will create a temporary table on disk, because text fields are deemed too large). Just a tip

Sohail Ahmed · Accepted Answer · 2012-10-12 07:13:24Z

0

Use these two functions while entring data to database and out from database.

/****************************************/
/* Encode special chars                 */
/*                                      */
/****************************************/

function DBin($string) 
{
    return  trim(htmlspecialchars($string,ENT_QUOTES));
}

/****************************************/
/* Decode special chars                 */
/*                                      */
/****************************************/

function DBout($string) 
{
    $string = trim($string);
    return htmlspecialchars_decode($string,ENT_QUOTES);
}

answered Oct 12, 2012 at 7:13

Sohail Ahmed

1,66714 silver badges23 bronze badges

2 Comments

Sohail Ahmed Over a year ago

it is for to enter data in mysql database. browser result = Bobby '); DROP TABLE users; -- page source = Bobby '); DROP TABLE users; -- database = Bobby '); DROP TABLE users; --

Elias Van Ootegem Over a year ago

fair point, forgot I was testing in browser, just saw all the quotes appear, forgot about htmlentities. But I maintain: prepared statements are still the best way to go

Collectives™ on Stack Overflow

Whats wrong with this INSERT query?

2 Answers 2

Update

Example

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

Example

2 Comments

2 Comments

Related