Jump to content


Photo

inser_id() { not inserting for me says wrong integer

php sql

Best Answer Jacques1, 09 January 2017 - 11:34 AM

You should construct dynamic queries with prepared statements. Not only does this solve a lot of security vulnerabilities. It would also fix your current problem without any extra checks, because if $attributes['id'] is null, a prepared statement maps that to an SQL NULL, which is a perfectly valid value for an auto-incremented integer column.

 

Your code, on the other hand, wraps all values in quotes, so you end up trying to insert an empty string into an integer column.

Go to the full post


  • Please log in to reply
5 replies to this topic

#1 Michael_Baxter

Michael_Baxter
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts
  • LocationUK

Posted 08 January 2017 - 08:28 PM

Hi I have been working on my OOP and have put together some class files to aid my test application ( photo album )

on the upload page I have the browse box,

a caption text box and an upload button this page posts to self,

Once you click on upload it is also supposed to insert a database entry to allow tracking of the file's attributes,

once I click the upload button I get this error message back,

" Database Query Failed: Incorrect integer  value ' ' for column 'id' at row 1 "

 so I have re looked over my codes in regards to uploading files and just can not seem to put my mouse on the spot that's causing me an issue so here is the codes that matter to the file uploads...

 

this one is from my database.php class file,

 public function insert_id() {
    // get the last id inserted over the current db connection
    return mysql_insert_id($this->connection);
  }

and this one is one comes from my photograph class file,

	
	public function create() {
		global $database;
		$attributes = $this->sanitized_attributes();
	  $sql = "INSERT INTO ".self::$table_name." (";
		$sql .= join(", ", array_keys($attributes));
	  $sql .= ") VALUES ('";
		$sql .= join("', '", array_values($attributes));
		$sql .= "')";
	  if($database->query($sql)) {
	    $this->id = $database->insert_id();
	    return true;
	  } else {
	    return false;
	  }
	}

Looking at my error and the information in those functions I can guess that's where the issue is coming from just don't get why any ideas please?



#2 benanamen

benanamen
  • Members
  • PipPipPip
  • Master Coder
  • 1,317 posts

Posted 08 January 2017 - 10:58 PM

Not even going to try and decipher your code. If you're doing an INSERT you have no need to know what the last insert id is.


Edited by benanamen, 08 January 2017 - 11:00 PM.

To save time, let's just assume I am never wrong.

The XY Problem
The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.


Make A Donation https://www.paypal.me/KevinRubio

 

"This text has been encoded with ROT26. If you can read this you must have found a backdoor. Congratulations!"


#3 requinix

requinix
  • Administrators
  • Forgotten Administrator
  • 8,527 posts
  • LocationWA

Posted 09 January 2017 - 01:40 AM

The ID is being included in $attributes but doesn't have a valid value. Don't include it. Or if you must, set it to null.

#4 Michael_Baxter

Michael_Baxter
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts
  • LocationUK

Posted 09 January 2017 - 10:51 AM

yes I had a feeling after I was looking over the codes after I made this post that was going to be the case just was tired and my eyes hurt after staring at the screen so simply wanted to ask others advice



#5 Jacques1

Jacques1
  • Gurus
  • Turtles all the way down
  • 3,583 posts

Posted 09 January 2017 - 11:34 AM   Best Answer

You should construct dynamic queries with prepared statements. Not only does this solve a lot of security vulnerabilities. It would also fix your current problem without any extra checks, because if $attributes['id'] is null, a prepared statement maps that to an SQL NULL, which is a perfectly valid value for an auto-incremented integer column.

 

Your code, on the other hand, wraps all values in quotes, so you end up trying to insert an empty string into an integer column.



#6 Michael_Baxter

Michael_Baxter
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts
  • LocationUK

Posted 09 January 2017 - 10:38 PM

ok thanks Jaques1 that makes sense.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users