Insert SQL code into database in PHP/MySQL

Question

I'm trying to write sql to insert a SQL code into one of the table's columns. The table has these three columns: email, verification code, sql.

I try this code, and variations of it, playing around with the quotes and backslashing/escaping them, etc... but something's still wrong:

INSERT INTO pre_registration(email, verification_code, sql) VALUES('[email protected]', '8efb100a295c0c690931222ff4467bb8', '"INSERT INTO customer(title) VALUES(\'Mr.\')"')

It tells me there's an error in the SQL syntax:

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql) VALUES('[email protected]', '89f0fd5c927d466d6ec9a21b9ac34ffa', "INSER' at line 1

How to do it? I'm using PHP/MySQL.

What exact error does it report? And on what RDBMS? It actually looks like it would work correctly for most... — Michael Berkowski
– Michael Berkowski, Commented Dec 17, 2012 at 14:11
I've edited the question to include the error. I'm using php/MySQL. — user961627
– user961627, Commented Dec 17, 2012 at 14:12
SQL is a MySQL reserved keyword You must quote it in backticks to use as a column name. — Michael Berkowski
– Michael Berkowski, Commented Dec 17, 2012 at 14:13

kmkaplan · Accepted Answer · 2012-12-17 14:17:22Z

4

MySQL considers sql as a keyword. You have to quote it:

INSERT INTO pre_registration(email, verification_code, `sql`) VALUES('[email protected]', '8efb100a295c0c690931222ff4467bb8', '"INSERT INTO customer(title) VALUES(\'Mr.\')"')

By the way double the quotes to escape them instead of using bakslashes. This is more SQL friendly.

INSERT INTO pre_registration(email, verification_code, `sql`) VALUES('[email protected]', '8efb100a295c0c690931222ff4467bb8', '"INSERT INTO customer(title) VALUES(''Mr.'')"')

edited Dec 17, 2012 at 14:17

answered Dec 17, 2012 at 14:12

kmkaplan

19k4 gold badges55 silver badges65 bronze badges

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

2 Comments

Michael Berkowski Over a year ago

In MySQL quotes can be backslash-escaped

kmkaplan Over a year ago

@MichaelBerkowski OK, I have retagge the question.

tacticsinquestion · Accepted Answer · 2012-12-17 14:24:50Z

Some insight into the exact SQL error would help. At first glance I'd say you need to apply spaces between the table name and the open parentheses and between values and the open parentheses.

Also, the Single quotes around the double quotes for the SQL portion may be creating an error though I am not certain. Whatever is between the single quotes is interpreted literally which should make the escape characters actually be slashes inside the stored data.

Also, sql is a reserved word that must be quoted for use.

Finally, depending on your situation there may be a more secure method of data entry using prepare and bound parameters:

try
{
    $conn = new PDO ( "sqlsrv:server = $serverstringname; Database = $logindatabase", "$loginusername", "$loginpassword");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

catch ( PDOException $e )
{
    print( "Error connecting to SQL Server." );
    die(print_r($e));
}

$email = '[email protected]' //or some other way of setting the variable like $_POST
$verification_code = '#####' //or $_Post method
$sql = 'Put Query Here' //probably have to declare this explicitly

$sql_insert = "INSERT INTO pre_registration_info (email, verification_code, 'sql') VALUES (?,?,?)";
        $stmt = $conn->prepare($sql_insert);
        $stmt->bindValue(1, $email);
        $stmt->bindValue(2, $verification_code);
        $stmt->bindValue(3, $sql);
        $stmt->execute();

Collectives™ on Stack Overflow

Insert SQL code into database in PHP/MySQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related