sqlite query change question marks to NULL (?)

ted_chou12 · 04-22-2011, 04:17 PM

Hi, I got the query insertion and everything correctly:

Code:

		$db = DBI->connect("DBI:SQLite2:dbname=/var/db/blog1.db");
		$read = $db->prepare("SELECT * FROM blog WHERE id='$id'");
		$read->execute();
		@rows = $read->fetchrow();
		if ((scalar(@rows) == 0) && ($id ne "") && ($til ne "") && ($descrip ne "") && ($pubDate[$i] ne "") && ($link[$i] ne "")) {
			$db->do("INSERT INTO blog (id, title, content, datetime, link) values (\"$id\", \"$til\", \"$descrip\", \"$pubDate[$i]\", \"$link[$i]\")");
}

This is the actual query, for everywhere there are question marks for eg:
This is my content?
Changes to:
This is my contentNULL

I also tried the query with:
"INSERT INTO blog (id, title, content, datetime, link) values (\"?\", \"?\", ?, \"?\", ?)"
for 1,2,4 the result is NULL, for 3,5 the result is empty (nothing was inserted into the table.
Thanks,
Ted

stickman · 04-22-2011, 05:48 PM

Check how your columns are defined. For example, are you trying to insert a question mark into an integer field?

ted_chou12 · 04-22-2011, 08:07 PM

Hi, the type are as follow:

id INTEGER 0 Edit Delete Index Unique
title VARCHAR(150) 0 Edit Delete Index Unique
tags VARCHAR(200) 0 Edit Delete Index Unique
content TEXT 0 Edit Delete Index Unique
datetime TIMESTAMP 0 Edit Delete Index Unique
link VARCHAR(400) 0 Edit Delete Index Unique

I found something interesting thing:
only certain row actually have NULL, but they are inserted exactly the same loop :/

blog.php?user=ted_chou12&id=60
blog.phpNULLuser=ted_chou12&id=52
blog.phpNULLuser=ted_chou12&id=51
blog.phpNULLuser=ted_chou12&id=49
blog.php?user=ted_chou12&id=48
blog.phpNULLuser=ted_chou12&id=47
blog.phpNULLuser=ted_chou12&id=46
blog.php?user=ted_chou12&id=45
blog.phpNULLuser=ted_chou12&id=44
blog.phpNULLuser=ted_chou12&id=43
blog.php?user=ted_chou12&id=42
blog.phpNULLuser=ted_chou12&id=41
blog.phpNULLuser=ted_chou12&id=40
blog.php?user=ted_chou12&id=39
blog.php?user=ted_chou12&id=38
blog.php?user=ted_chou12&id=37
blog.phpNULLuser=ted_chou12&id=36
blog.phpNULLuser=ted_chou12&id=35
blog.php?user=ted_chou12&id=34
blog.phpNULLuser=ted_chou12&id=33
blog.phpNULLuser=ted_chou12&id=32
blog.php?user=ted_chou12&id=31
blog.php?user=ted_chou12&id=30
blog.phpNULLuser=ted_chou12&id=29

ted_chou12 · 04-25-2011, 02:36 AM

Hi, does anyone has any ideas? I have searched online and found one thread related so far, but it doesn't have solution.
Thanks,
Ted

Guttorm · 04-25-2011, 05:36 AM

Hi

Read this guide:

http://www.perl.com/pub/1999/10/DBI.html

In particular the last section "Don't do this". That's what your script is doing. Use ? for parameters in your SQL query and pass the parameters to the execute call.