Making User Input Safe

[N-Bomb(Nerd)]

Hello, I'm quite excited as I've finally created my first full working script that actually has some meaning to it! There's just one final thing left before I can put a halt to the php scripting and start designing the website.

 

I'm getting some user input on my website, and I'm trying to figure out the safest way to store their input into my database. I've never created a public website before so I'm not sure what's really possible as far as the sql injections go. To be honest though I'm quite horrified about getting my sql injected ..

 

There is only one part in my script where their input is actually outputted back to them and it's wrapped in the htmlentities(). However, I believe they're able to inject still even if I don't output anything to them.. what would be the best thing to do with my input before submitting it to my database? I'm looking for a sure method of defeating all sql injections..

 

Thanks,

N

[Mchl]

Best thing to do is to use prepared statements

 

Second best is to escape any user input variables that are put into query strings.

[N-Bomb(Nerd)]

I'm not quite sure what prepared statements are, however I've heard of escaping. What all would I have to do to properly escape my string, would this be 100% protection from sql injection?

[Mchl]

http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html

[N-Bomb(Nerd)]

http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html

 

Heh, that actually ended up confusing me even more somehow.  :'(

[Mchl]

Bottom line is: mysql_real_escape_string is not 100% sure way to protect your queries from SQL injection.

Prepared statements are, because of the way they work.

- first you create and send to database a query 'template' with '?' instead of actual variables. It is precompiled by MySQL, so that it structure can not be changed.

- then you send variables to be put into a query in place od '?' placeholders.

 

If used properly this give you total protection from SQL injection.

[N-Bomb(Nerd)]

Bottom line is: mysql_real_escape_string is not 100% sure way to protect your queries from SQL injection.

Prepared statements are, because of the way they work.

- first you create and send to database a query 'template' with '?' instead of actual variables. It is precompiled by MySQL, so that it structure can not be changed.

- then you send variables to be put into a query in place od '?' placeholders.

 

If used properly this give you total protection from SQL injection.

 

That seems to make a bit more sense, thanks! 

 

However, I've never really worked with this and I just starting learning the mysql functions now I need mysqli? I tried looking at a few examples and came up with this, doesn't seem to be working and it's outputting "broke".

 

$conn = mysqli_connect($Host, $Username, $Password, $Database);
if (!mysqli_connect_errno()) {
if ($stmt = mysqli_prepare($conn, "SELECT `First_Name`, `Last_Name` FROM INFO WHERE Id=?")) {
	mysqli_stmt_bind_param($stmt, "s", $this-> Id);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $First, $Last);
	mysqli_stmt_fetch($stmt);
	mysqli_stmt_close($stmt);

	echo 'First Name: ' . $First . '<br>Last Name: ' . $Last;
} else {
	echo 'broke';
}
mysqli_close($conn);
}

[Mchl]

Yeah, prepared statements are only supported by mysqli. Worry not. It works mostly like mysql, except it's better

 

I think this

mysqli_stmt_bind_param($stmt, "s", $this-> Id);

 

should be

 

mysqli_stmt_bind_param($stmt, "s", $this->Id);

[N-Bomb(Nerd)]

Yeah, prepared statements are only supported by mysqli. Worry not. It works mostly like mysql, except it's better

 

I think this

mysqli_stmt_bind_param($stmt, "s", $this-> Id);

 

should be

 

mysqli_stmt_bind_param($stmt, "s", $this->Id);

 

I just tried this and ran the function and it keeps outputting "broke", so the else on the if statement is firing.. so I believe something is wrong with this line:

 

if ($stmt = mysqli_prepare($conn, "SELECT `First_Name`, `Last_Name` FROM INFO WHERE Id=?")) {

[Mchl]

Try echoing mysqli_error instead of 'broke'

[N-Bomb(Nerd)]

Try echoing mysqli_error instead of 'broke'

 

Psh, are you kidding? Everyone loves having errors in their script and having it output as "broke".

 

Anyways, I echoed mysqli_error and received this output:

Unknown command

broke

 

FYI, I kept the "broke" echo just because it's now officially considered the cool way of trying to debug your script. 

 

Edit: I changed mysqli_error to mysqli_errno and got the output: "1047"

[Mchl]

http://bugs.php.net/bug.php?id=30656

 

Which MySQL Server version do you have?

[N-Bomb(Nerd)]

http://bugs.php.net/bug.php?id=30656

 

Which MySQL Server version do you have?

 

So, I'm guessing I should make my database MySQL 5.0 instead of MySQL 4.0?

 

I have the option to do so in my control panel, would simply changing it do anything to my database or will I need to reconstruct it?

[Mchl]

Ahh... yess...

This bug report was filled in 2004 and MySQL 4.0 was outdated THEN!

As for updating you should ask your hosting company probably. I've never used any automated migration tools for MySQL. Just make sure you do a backup prior to anu updates

[N-Bomb(Nerd)]

All seems to be working well with the creation of the new database. I have one more question, and I believe I'll be good to go..

 

After this:

if ($stmt = mysqli_prepare($conn, "SELECT `First_Name`, `Last_Name` FROM INFO WHERE Id=?")) {

 

is ran, is there a way to figure out if the query was ran successfully and the Id existed and the vaules for `First_Name` and `Last_Name` was pulled?

 

In my previous script with only mySQL I had something like:

if ($Query) {
if (mysql_num_rows($Query) > 0) {
	// Value exists..	
} else {
	// Value doesn't exist..
}
}

[Mchl]

http://www.php.net/manual/en/mysqli-stmt.num-rows.php

 

Take a close look at the examples. You need to store result before calling this.

[fenway]

Prepared statements aren't great for performance, though... they're per-connection.

 

Also, if you properly escape special characters -- with mysql_real_escape_string() or your own personal function -- it doesn't get any *safer*.