[RESOLVED] Assigning SQL to variable not working

[amy_damnit]

As I am learning PHP from Larry Ullman's book, I am trying to streamline his code.

On an example in chapter 12, I tried assigning SQL to a variable, but it doesn't work as expected.

Here is the SQL...

Does NOT work

			$title = $_POST['title'];
			$entry = $_POST['entry'];

		$query = "INSERT INTO blog_entries (
					blog_id,
					title,
					entry,
					date_entered)
					VALUES (
					0,
					$title,
					$entry,
					NOW())";

Does work

			$query = "INSERT INTO blog_entries (
						blog_id,
						title,
						entry,
						date_entered)
						VALUES (
						0,
						'{$_POST['title']}',
						'{$_POST['entry']}',
						NOW())";

Here is where the variable is called...

				// Execute the query.
				if (@mysql_query($query)) {
					print '<p>The blog entry has been added.</p>';
				} else {
					print "<p>Could not add the entry because: <b>"
						. mysql_error()
						. "</b></p>"
						. "<p>The query was $query.</p>";
				}

It appears the problem is that MySQL is expecting title and entry to be enclosed with single quotes ''.

How can I make the more streamlined code in the second example work??

Thanks,

Amy

[bradgrafelman]

amy.damnit wrote:

It appears the problem is that MySQL is expecting title and entry to be enclosed with single quotes ''.

Well of course it does; the data for those two columns are strings (I'm assuming), and strings (just like in PHP) must be quoted.

amy.damnit wrote:

How can I make the more streamlined code in the second example work??

Do you mean the first example, the one with the $title and $entry variables? If so, the answer is simple - add the single quotes like you did in the other example.

EDIT: Also, if blog_id is an AUTO_INCREMENT column (which I suspect it is, or should be at least) then you should simply leave it out of your INSERT queries entirely; the DBMS (MySQL, for example) should take care of its value automatically.

[amy_damnit]

bradgrafelman;10914863 wrote:

Do you mean the first example, the one with the $title and $entry variables?

That is what I meant.

bradgrafelman;10914863 wrote:

If so, the answer is simple - add the single quotes like you did in the other example.

Won't the quotes be treated as literals and mess up the nested variables (i.e. $title and $entry)??

bradgrafelman;10914863 wrote:

EDIT: Also, if blog_id is an AUTO_INCREMENT column (which I suspect it is, or should be at least) then you should simply leave it out of your INSERT queries entirely; the DBMS (MySQL, for example) should take care of its value automatically.

Okay, but that is what my book said to do.

Amy

[bradgrafelman]

amy.damnit wrote:

Won't the quotes be treated as literals and mess up the nested variables (i.e. $title and $entry)??

Yes, the single quotes will be treated as literal single quotes and be included in the string. No, throwing single quotes in the middle of a string won't change how PHP interprets variables. The only time variable interpolation is not done is when the string itself is delimited by single quotes.

amy.damnit wrote:

Okay, but that is what my book said to do.

I figured as much. Unfortunately, the problem with the many PHP/MySQL/etc. books out there is that anyone who sets out to publish a book can do so. There's no committee that meets and decides whether the book contains 100% correct, up-to-date, un-deprecated suggestions at the time. Furthermore, what may have been "OK" at the time the book was written (e.g. register_globals) may now be grossly inaccurate, deprecated, or just-plain-wrong.

As for my suggestion above, I couldn't find any manual reference that suggests omitting it (though I did find one stating that specifying NULL or 0 causes MySQL to behave as if no value had been passed and a new one is generated). Semantically, however, I think omitting the column from the queries makes more sense; not specifying a value at all means its up the DBMS to handle it, whereas seeing a 0 in a query might not be quite as clear.

[amy_damnit]

Brad,

bradgrafelman;10914868 wrote:

Yes, the single quotes will be treated as literal single quotes and be included in the string. No, throwing single quotes in the middle of a string won't change how PHP interprets variables. The only time variable interpolation is not done is when the string itself is delimited by single quotes.

Tricky! Yah, that is why I was confused. I just remebered that single quotes = "literal interpretation", but I see the difference now.

I figured as much. Unfortunately, the problem with the many PHP/MySQL/etc. books out there is that anyone who sets out to publish a book can do so. There's no committee that meets and decides whether the book contains 100% correct, up-to-date, un-deprecated suggestions at the time. Furthermore, what may have been "OK" at the time the book was written (e.g. register_globals) may now be grossly inaccurate, deprecated, or just-plain-wrong.

Understood.

As for my suggestion above, I couldn't find any manual reference that suggests omitting it (though I did find one stating that specifying NULL or 0 causes MySQL to behave as if no value had been passed and a new one is generated). Semantically, however, I think omitting the column from the queries makes more sense; not specifying a value at all means its up the DBMS to handle it, whereas seeing a 0 in a query might not be quite as clear.

Okay, fair enough. I'll do that moving forward.

Thanks for the quick responses!

Amy
🙂

[bradgrafelman]

Glad I could help. Don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu.

By the way... just out of curiosity, which part of Illinois are you from? :p

[amy_damnit]

Brad,

I'm not sure what the notation is called, but when you name the fields and the values, how would your suggestion change my SQL?

Would it be like this... (i.e. Omitting field and value)

			$query = "INSERT INTO blog_entries (
						title,
						entry,
						date_entered)
						VALUES (
						'$title',
						'$entry',
						NOW())";

Or like this... (i.e. Omitting just the field)

			$query = "INSERT INTO blog_entries (
						blog_id,
						title,
						entry,
						date_entered)
						VALUES (
						'$title',
						'$entry',
						NOW())";

Amy

[bradgrafelman]

It'd be the first one; the number of items in the first () group must match the number in the second. Think of it as an associative array, defining the keys in the first () group and the values in the second. In order to completely remove the blog_id reference, you'd first remove it's "key" from the first () group and then remove its "value" from the second group.

[bradgrafelman]

Also, just in case your book doesn't cover it (which I sure hope it does - otherwise you might want to throw it in the trash and find a new one), your code is vulnerable to SQL injection attack. User-supplied data should never be placed directly into a SQL query; instead you must first sanitize it with a function such as [man]mysql_real_escape_string/man (or just use a newer library such as [man]MySQLi[/man] or [man]PDO[/man] with prepared statements).

Like I said, your book might cover it and it's just in a later chapter/section.

[amy_damnit]

bradgrafelman;10914870 wrote:

By the way... just out of curiosity, which part of Illinois are you from? :p

The soft, squishy center originally... 🙂

[bradgrafelman]

I just ask because I live about 20 minutes west of Peoria. It's nice to finally meet someone from Illinois who can't see the Sears Tower out their window. :p

[amy_damnit]

bradgrafelman;10914873 wrote:

Also, just in case your book doesn't cover it (which I sure hope it does - otherwise you might want to throw it in the trash and find a new one), your code is vulnerable to SQL injection attack. User-supplied data should never be placed directly into a SQL query;

Looks like I better find my trash can! 🙂

instead you must first sanitize it with a function such as [man]mysql_real_escape_string/man (or just use a newer library such as [man]MySQLi[/man] or [man]PDO[/man] with prepared statements).

Since you brought this up, can you explain it more? You are saying that taking data from a form and plugging it directly in an SQL statement is dangerous?? :queasy:

Is that because someone could put in a nefarious SQL string within my outside SQL statement?

What do the approaches you mentioned do?

Like I said, your book might cover it and it's just in a later chapter/section.

Well, actually, it is chapter 12 of 13, and I'm skipping chapter 13!

The book is by Larry Ullman and is called "PHP for the World Wide Web, 2nd Edition"

It is a beginner book, and seemed pretty good, but certainly isn't for pros like you.

Tomorrow I'm ready Larry Ullman's "PHP6 and MySQL 5" which is more advaced.

Then after that, it's off to Larry Ullman's "PHP 5 Advanced".

Then after that I have some other books in mind. 🙂

I am very concerned (and aware) of security, so if you know of any good books or website or articles, I am very open to them!!

Gee, there sure is a lot to learn!!!

Amy

[bradgrafelman]

amy.damnit wrote:

Since you brought this up, can you explain it more? You are saying that taking data from a form and plugging it directly in an SQL statement is dangerous??

Is that because someone could put in a nefarious SQL string within my outside SQL statement?

In a nutshell, yes, that is one implication. For example, let's say you were coding a user login system and in order to verify username/passwords, you ran this query:

$sql = "SELECT userid FROM users WHERE username='$_POST[user]' AND password='$_POST[pass]'

Now, let's say me, being the malicious hacker I am, decide to send 'admin' as the user and this as my password:

asdf' OR 'a'='a

Your query would then look like:

SELECT userid FROM users WHERE username='admin' AND password='asdf' OR 'a'='a'

Obviously, 'a'='a' is always true and thus the boolean logic for the query will be true despite the fact that 'asdf' isn't the password.

Another implication is that your SQL query strings could simply break. If you had a form for users to enter their real names, for example, and someone enters John O'Reilly, the single quote in the name would break your SQL query string (since strings are normally delimited by single quotes in the first place).

What functions such as [man]mysql_real_escape_string/man do is simply 'escape' (or prepend with a backslash) characters that would have special meanings (or adverse effects) if used in a SQL query. If you go the route of PHP5's newer MySQLi or PDO libraries and prepared statements, you simply use placeholders in the SQL query string; later, when you 'bind' variables/data to these placeholders, the DBMS's driver will automagically apply the proper escaping for you.

Note that in some instances, other security measures might be more applicable. For example, if you passed an id in the URL and wanted to use it in a query, I would suggest this approach:

$id = (isset($_GET['id']) ? (int)$_GET['id'] : NULL);

In other words, casting the id to an integer will prevent any unwanted SQL injections. Plus, since numeric data doesn't need to be quoted in SQL queries, casting it to an integer would be better than using mysql_real_escape_string() since you know that all non-numeric characters will be thrown out after it's been casted to an (int).

amy.damnit wrote:

I am very concerned (and aware) of security, so if you know of any good books or website or articles, I am very open to them!!

Unfortunately I'm not aware of recent publications and whatnot - I just learn it all as I go.

EDIT: If you're just now learning SQL, one thread I might suggest you peruse would be this one. That way, if you ever come across a tutorial or example code where 'ORDER BY RAND()' is used to obtain random results, you'll simply sigh at the poor author who hasn't ever written a truly scalable piece of software.

[amy_damnit]

bradgrafelman;10914875 wrote:

I just ask because I live about 20 minutes west of Peoria. It's nice to finally meet someone from Illinois who can't see the Sears Tower out their window. :p

Definitely a "country girl".

Yes, there is life beyond Chicago.

[amy_damnit]

bradgrafelman;10914877 wrote:

In a nutshell, yes, that is one implication. For example, let's say you were coding a user login system and in order to verify username/passwords, you ran this query:

$sql = "SELECT userid FROM users WHERE username='$_POST[user]' AND password='$_POST[pass]'

Now, let's say me, being the malicious hacker I am, decide to send 'admin' as the user and this as my password:

asdf' OR 'a'='a

Your query would then look like:

SELECT userid FROM users WHERE username='admin' AND password='asdf' OR 'a'='a'

Obviously, 'a'='a' is always true and thus the boolean logic for the query will be true despite the fact that 'asdf' isn't the password.

Wow! :eek:

Another implication is that your SQL query strings could simply break. If you had a form for users to enter their real names, for example, and someone enters John O'Reilly, the single quote in the name would break your SQL query string (since strings are normally delimited by single quotes in the first place).

What functions such as [man]mysql_real_escape_string/man do is simply 'escape' (or prepend with a backslash) characters that would have special meanings (or adverse effects) if used in a SQL query. If you go the route of PHP5's newer MySQLi or PDO libraries and prepared statements, you simply use placeholders in the SQL query string; later, when you 'bind' variables/data to these placeholders, the DBMS's driver will automagically apply the proper escaping for you.

I see that I have a lot to learn!!

Thanks for the thoughts.

Unfortunately I'm not aware of recent publications and whatnot - I just learn it all as I go.

Well, if you see me around, feel free to critique my code.

EDIT: If you're just now learning SQL, one thread I might suggest you peruse would be this one. That way, if you ever come across a tutorial or example code where 'ORDER BY RAND()' is used to obtain random results, you'll simply sigh at the poor author who hasn't ever written a truly scalable piece of software.

I know SQL, but I've been away from coding for a few years, and I'm new to web programming, so it's like I'm starting all over?!

Thanks for all of your help!

Amy