Insert ' character in sql

Question

I'm taking data from an external XML file, so I cannot make changes. Also I need all special characters.

This is XML

<item>
<name>Lucy</name>
<embed><iframe src='http://website.com style='something:somehow'></iframe></embed>
</item>

This is sql table

$sql = "CREATE TABLE IF NOT EXISTS `models`(
    `model_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `model_username` VARCHAR(250) NOT NULL,
    `model_embed` TEXT NOT NULL )";

XML into SQL

foreach ($items as $item) {
       $name=$item->name;
       $iframe=$item->embed;

$ins = " INSERT INTO `models` (`model_username`, `model_embed`)
         VALUES ('$name', '$iframe')
}

And I get this error

1064 : 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 'http://website.com style='something:somehow' at line 9

@Fred-ii- Would this not cause a parse error over a SQL Error? — Daryl Gill, Aug 18 '14 at 19:30
Though after re-reading the question body (like twice) I agree that this is not SQL Injection related. +1 to reopen also — Daryl Gill, Aug 18 '14 at 19:32
@DarylGill Give it a go, the question's been reopened. You have my blessing ;) I'm going for another Espresso. *Cheers* — Funk Forty Niner, Aug 18 '14 at 19:37
Well, it is absolutely SQL-Injection **related**. If the code wasn't vulnerable to SQLi, then the "problem" indicated by the question wouldn't exist. And if the problem exists, it means *by very definition* that the code is vulnerable to SQLi... — ircmaxell, Aug 18 '14 at 19:40
@ircmaxell Sure, I'll agree it's "related", but not the "cause" of the problem. I disagree. — Funk Forty Niner, Aug 18 '14 at 19:41
@Fred-ii- it's definitely the cause. The error indicates that injection is occuring. Yes, there are syntax errors in the code. But the error that the OP posted is not related to syntax errors. Which indicates that it's copy-paste errors that are incidental to the actual problem being asked about. — ircmaxell, Aug 18 '14 at 19:46
@ircmaxell With that said; I now agree. *However,* given the OP's experience, am thinking/hoping that he/she will be able to put it all together and use prepared statements. I will make an additional comment to that effect. — Funk Forty Niner, Aug 18 '14 at 19:49
Use [**`mysqli_*` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO with prepared statements**](http://php.net/pdo.prepared-statements). This will more than likely fix the problem and avoid injection at the same time, while adding the missing quote and semi-colon to your code, as previously stated. `('$name', '$iframe')";` <= — Funk Forty Niner, Aug 18 '14 at 19:51
@ClémentMalet: knowledge of sql injection and how to avoid it would solve this problem and solve any future similar problems OP will have. e.g. OP's hit his thumb with a hammer. we tell him "don't hit your thumb". Immediate problem solved, but OP will go on to smash the other 9 fingers, when our answer should have been "don't hit any of your fingers" — Marc B, Aug 18 '14 at 19:54
@MarcB I've no problem with the fact that it actually solves the problem, but with the fact that "This question already has an answer here" might not be a complete enough explanation on how using SQL Injection has to do with OP's problem. If you don't know anything about it, as it seems to be OP's case, you're very unlikely to understand why. Do you really think that closing as duplicate with nomore explanations is enough ? Or it is a common practice ? — Clément Malet, Aug 18 '14 at 20:02
@ClémentMalet:I could just close and link to bobby-tables.com, but you can't close with offsite links. — Marc B, Aug 18 '14 at 20:04

score 1 · Accepted Answer · edited May 23 '17 at 12:29

Though, @Fred-ii- takes the cake with spotting the incorrect closure reason

It appears you have a number of problems within your code.

The first, you are not closing your variables off correctly:

$ins = " INSERT INTO `models` (`model_username`, `model_embed`)
         VALUES ('$name', '$iframe')

A solution to this would be:

$ins = "INSERT INTO `models` (`model_username`, `model_embed`)
         VALUES ('$name', '$iframe')";

Notice the closing double quote and semi-colon as the end of the variable.

Though, the question closure relating to SQL injection was somewhat valid, as the error message states:

'http://website.com style='something:somehow'

As you are inputting the data directly into your query, you are encountering formatting errors with pre-maturley closing your SQL Syntax which is firing some unexpected errors on your end. A solution would be to swap to prepared statements which can be utilized in MySQLi (MySQL Improved library) or PDO.

Use mysqli_ with prepared statements, or PDO with prepared statements.

I'm also thinking that the OP should also be using `stripslashes()` and to escape the input. That could be a contributing factor due to the quotes in `` or other input. — Funk Forty Niner, Aug 18 '14 at 19:43
while the PHP code does need fixing, the actual SQL error suggests that the query string DID get built and executed, and then sufered from an injection problem causing SQL syntax errors anyways. — Marc B, Aug 18 '14 at 19:56

score -1 · Answer 2 · answered Aug 18 '14 at 19:45

-1

Use the following function:

MYSQL_ESCAPE_STRING($String)

answered Aug 18 '14 at 19:45

Mina Ezeet

138
7

1

No. Don't use that. that function is both deprecrated/obsolete and **NOT** the correct solution to the problem. – Marc B Aug 18 '14 at 19:55

Insert ' character in sql

2 Answers2