Why is the DB on this forum so dodgy?

[AtW]

I view strings as array of bytes, do I need to escape numbers too?

[bogeyman]

I've yet to meet any other sort. Just look here - http://thedailywtf.com/forums/thread/87226.aspx - where they're all banging on about using escaped strings to pass into SQL statements. And these guys nearly all seem to think they're experts.

(If you have to question why escaping strings is bad, put yourself into the aforementioned category).

Do you mean that strings should never be passed through, and the query should be abstracted from the actual SQL call - if so, I entirely agree.

To illustrate...

If an app needs to return a record for a an Engineering Part, let's say, then the app should provide a GetPartRecordByNo(string partNo) method.

Rather than GetWhateverIWant("SELECT * FROM TABLE_PARTS");

It's only right!

[TheMonkey]

I use PHP quite regularly. My code DOES escape strings, but safely:

	/**
	 * Login and set cookie etc.
	 */
	function security_login($email_address, $password, $remember_me) {
		$sql = sprintf("SELECT user_id FROM user WHERE email_address = '%s' AND password = MD5('%s');", 
			mysql_escape_string($email_address), 
			mysql_escape_string($password));
		print_r($this->database->querySingleRow($sql));
	}

Note - that is seriously unfinished part of the app's API.

[Cowboy Bob]

I use PHP quite regularly. My code DOES escape strings, but safely:

	/**
	 * Login and set cookie etc.
	 */
	function security_login($email_address, $password, $remember_me) {
		$sql = sprintf("SELECT user_id FROM user WHERE email_address = '%s' AND password = MD5('%s');", 
			mysql_escape_string($email_address), 
			mysql_escape_string($password));
		print_r($this->database->querySingleRow($sql));
	}

Note - that is seriously unfinished part of the app's API.

Ding, you're a winner!!! You should never escape strings to pass into DB queries - it's inherantly unsafe with regards to SQL injection. I know you're going to say that the mysql_escape_string escapes the string to make it safe, but how do you know it catches ALL eventualities? It's such a weak point, don't trust it. There are better ways. You should always use parameterized queries. I understand PHP has something called PearDB that can do this. A PreparedStatement is what it's called in the Java world.

[Cowboy Bob]

Do you mean that strings should never be passed through, and the query should be abstracted from the actual SQL call - if so, I entirely agree.

To illustrate...

If an app needs to return a record for a an Engineering Part, let's say, then the app should provide a GetPartRecordByNo(string partNo) method.

Rather than GetWhateverIWant("SELECT * FROM TABLE_PARTS");

It's only right!

Nope. It should be something like this - in whatever syntax you prefer:-

PreparedStatement stmt = connection.prepareStatement("select * from table_parts where partNo = ?");
stmt.setString(1, partNo);
stmt.execute();

This leaves the string escaping to the DB itself, which is a much better way of doing things as it is more likely to know what's best.

[TheMonkey]

Yeah the mysqli extension does that using recent mysql client libs. Only problem is that 90% of crappy php web hosts don't use it.

mysql_escape_string IS safe. I've read the source code!

[Dundeegeorge]

All very interesting indeed

but where's the abuse?
Or at least a picture of some tits? (cue the hysterical pictures of parus major)