insert strings on a SQLite database using the PHP exec()

Question

I'm trying to insert therms in a database using the exec function from the PHP's SQLite3 library and I'm having problems with strings:

foreach($xml->children() as $child) {
    $id = 0;
    $nome = "null";
    $partido = "null";
    $tag_localizacao = 0;

    foreach ($child->children() as $grandchild) {   
        switch($grandchild->getName()) {
            case "id":
                $id = $grandchild;
                break;
            case "nome":
                $nome = $grandchild;
                break;
            case "partido":
                $partido = $grandchild;
                break;
            case "tagLocalizacao":
                $tag_localizacao = $grandchild;
        }
    }
    $db->exec('INSERT INTO lista_deputados (id, nome, partido, tag_localizacao) VALUES ('.$id.','.$nome.', '.$partido.', '.$tag_localizacao.')');
}

$id and $tag_localizacao are integers, and it works perfectly. $nome and $partido are strings and don't work at all... when the string has just one word, like "test" the PHP thinks that it's a column and if has more words i don't know exactly what happens but the error message is:

Warning: SQLite3::exec(): near "Lopes": syntax error in C:\wamp\www\desafio\temp.php on line 30

In the case the string is "Adalclever Lopes".

I'm really not understanding what is happening, and I even tried to escape the string.

I'm not very familiar with PHP, but usually request parameters are not concatenated to a query, but are given as parameters. It also helps to avoid SQL injection. — Aleksei Zyrianov, Jul 17 '14 at 22:19
Here's a good explanation of how it should be done: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Aleksei Zyrianov, Jul 17 '14 at 22:22
Hey Alexey, thanks for the hints about SQL injection, but i'm not really worried about it right now... I tried to pass the value as function parameters but it didn't work... i kept getting the same results... — Pedro Maciel Corgozinho, Jul 17 '14 at 23:56
Learn how to use prepared statements now, before you create a security hole that you forget about. Additionally, prepared statements protect against values causing syntax errors, which seems to be what you are running into right now. — Colonel Thirty Two, Jul 18 '14 at 01:46

score 0 · Answer 1 · answered Jul 17 '14 at 22:42

0

try wrapping the strings in single quotes, like:

$db->exec("INSERT INTO lista_deputados (id, nome, partido, tag_localizacao) VALUES ($id,'".$nome."', '".$partido."', $tag_localizacao)");

you also don't need single quotes and dots around $id and $tag_localizacao, as PHP puts these in for you.

when you get syntax errors with a database query it's quite often worth printing it out and running it directly through the database to try and

answered Jul 17 '14 at 22:42

jam3

129
5

It not solve the problem... I just got this error message: ( ! ) Parse error: syntax error, unexpected '"' in C:\wamp\www\desafio\temp.php on line 33 – Pedro Maciel Corgozinho Jul 17 '14 at 23:41
Also... I tried to print the values and I guess i was ok... I mean the values were right... And I tried to insert the "Adalclever Lopes" string manually on the data base and even tried to insert it with the exec function using the string itself... everything worked... – Pedro Maciel Corgozinho Jul 17 '14 at 23:51

insert strings on a SQLite database using the PHP exec()

1 Answers1