dougv.com « Doug Vanderweide

Received in my email today:

Hi

say your blog and thought you might help.

strsql = “SELECT StaffID, DesignationID, StaffName, Password, ShopID from staffT where StaffName =” & UserName.Text & ” AND Password =” & Password.Text & “”

from the string, the username.text and password.text are form controls. what is happening is there are passing null values regardless of what you input in the text boxes resulting in a system error.

“System Error Object reference not set to an instance of an object”

Am using Mysql as the database.

I’m always glad to answer such questions, especially when the questioner is flirting with disaster, as much as this questioner is.

A trained eye can immediately spot the problem with the SQL statement above, aside from the problem of NULL values tossing errors. Namely, it’s wide-open to SQL injection. (And an even keener eye will note that the values for user name and password aren’t delimited with single-quotes.)

So here’s my reply email to the questioner:
Continue reading: The Basics Of Avoiding MySQL Injection Attacks In ASP.NET Web Forms »

Asked recently on Yahoo! Answers:

10 Points…Help MYSQL?
i have a table named ‘Students’ and it has the coulumns ‘Name’,'DateBirth’,'Age’ how do i do to know how many years, months, and days do all the students united have, THANKS

What the questioner wants to do is fairly complicated in MySQL. If we were working with a program, such as a PHP script or C++ program, we might well simply grab all the user records and do our calculations in the program. But in MySQL itself, this sort of query isn’t easy to write; it really requires a stored procedure.

Stored procedures (also called stored routines) are, as the name suggests, a way to save your queries on the database server, so you can call them over and over again without having to rewrite them in your code. Another benefit they offer is the ability to run several queries in sequence, and / or process data; in some cases, you can conditionally run a query based on the results of a previous query, or “roll back” the results of previous queries if some portion of the stored procedure didn’t properly work.

Stored procedures are also more secure than text-based queries, as a rule, because you can control permissions to the stored procedure itself — that is, limit only certain database users or roles to being able to execute (or call) the procedure. Also, stored procedures use parameters — that is, the variables you send to a stored procedure are given a type and are often sanitized by the database, which help prevents some of the worries about SQL injection attacks.

You will occasionally hear tell of functions in MySQL. They’re effectively the same thing as a stored procedure; the difference is that a function always returns a value, while a stored procedure may not. Think of stored procedures as null / void functions (C-like languages) or subroutines (Visual Basic).

Stored procedures are fairly unusual to most MySQL users; just about everyone works in text queries. MySQL has far more power than that, including robust support for stored procedures.

However, the management tools for MySQL tend to be weak in terms of support for stored procedures and functions, and that is especially true of phpMyAdmin. Generally speaking, you can get phpMyAdmin to create, alter, delete and call stored procedures via SQL queries; but you cannot visually manage them through the interface. **

The MySQL Administrator GUI better manages stored procedures and functions, but you need to be able to remotely connect to your MySQL server to use it, and that’s often not possible with most shared Web hosting. (It is possible with GoDaddy and Dreamhost, my preferred LAMP shared hosting providers.)

With that described, let’s proceed with the stored procedure that handles this request.

Continue reading: A MySQL Stored Procedure To Return The Total Years, Months And Days Of All Person Records »

Recently asked on Yahoo! Answers:

Using session_register() to register objects?
I’ve just started with OOP, and I’m making an app that uses objects that need to retain their data. Looking for an answer to this, I’ve discovered serialization. Whenever I go to a tutorial page about it, they always seem to mention the use of session_register() to automatically serialize and unserialize objects with PHP’s session feature. For example, the bottom 3 paragraphs of this page http://www.php.net/manual/en/language.oo…

However, I can’t seem to find ANY solid tutorials or instruction on how to accomplish this. How do I use session_register() to automatically have all my objects serialized?

Normally, I would answer this question within Answers itself, because the short answer is direct: You don’t need to call session_register() in order to create an object with session scope. You can simply assign any object to a session variable using the $_SESSION superglobal.

Consider this PHP code:

$bar = "hello world";
$_SESSION['foo'] = serialize($bar);
echo unserialize($_SESSION['foo']); //will echo "hello world"

Using direct assignment to a $_SESSION superglobal seems preferable over using session_register(), since (as the documentation states) session_register() will automatically unserialize the variable on every page, requiring your class to be present on every page in the application.

I’ve tested this: If you explicitly serialize an object and explicitly assign it to the $_SESSION superglobal, you need not have your class present on every page. However, if you use session_register(), and pass the variable through a page that does not include the class, it is effectively destroyed.

I’d like to expand here, as well, on session_register(): why it was created, why it remains, and more specifically, why maintaining the minimum scope necessary for each variable keeps your applications secure.

To do that, I need more room than is afforded in a Yahoo! answer, where anything over a few sentences easily falls into the tl;dr realm. (And contrary to the opinions voiced on Urban Dictionary, it’s not trite or disingenuous to tell a blowhard he needed to shut up five minutes ago; witness Edward Everett vs. Abraham Lincoln.)

By now, anyone but the n00biest n00b in the P & D category knows if I link to this blog, he’s going to get a long answer.

Continue reading: PHP’s session_register() And The Fine Art Of Variable Poisoning »

Asked recently on Yahoo! Answers:

Multiple nested MySQL functions in PHP?

I was going through code today, trying to make some things more compact. The application worked without problems, so I knew that mysql error statements were superfluous.

This is the type of statement I was changing, I figured that I’d remove the seemingly unnecessary $result2 variable

$result2 = mysql_query($carts) or die(mysql_error());<br />
while ($row2 = mysql_fetch_array($result2)) {<br />
	echo "<option>$row2[0]</option>";<br />
}

so this is what I changed it into (I basically replaced where it said $result2, to what $result2 had contained, and removed the error check)

while ($row2 = mysql_fetch_array(mysql_query($carts))) {<br />
	echo "<option>$row2[0]</option>";<br />
}

but this code returned infinite loops, much to my surprise. Why is it doing this? is there a way around it?

Recently, I wrote about how PHP’s weak data types often can lead to problems for new programmers who don’t understand the difference between null, empty and zero-length variables. Here’s another opportunity to expose why strong data typing is essential for best programming practices, and to show how PHP’s weak data types — normally, a source of comfort for beginning programmers — can be the source of extensive frustration.

Continue reading: The Trouble With PHP’s Weak Data Types: An Example Examined And Explained »