What is 'prepare' and why question marks?

[wilsonf1]

I'm currently learning php as i'm a classic ASP man!I've just generated a class that looked at one of my DB Tables and created load of functions like get, delete, save - all functions of a CMS.However, the code has prepare functions wrapping up the SQL, and instead of putting the $id variable in the query, it puts a ? instead:$query = $db->prepare("DELETE FROM `www_xxx_com_cms` WHERE `Id`=? LIMIT 1;");Based on me sending through my own database object for $db, I then got the error: Fatal error: Call to undefined method dbConnection::prepare()I could quite easily modify that line to this and it would work, but why is it like that in the first place?:$db->sql = "DELETE FROM `www_xxx_com_cms` WHERE `Id`=$id LIMIT 1;";

[justsomeguy]

That's what prepared statements do. You put placeholders in the query for the values that eventually go in there, and then you pass in the values using a different method on the database class. The database class will build the query so that the values are safe to use.

[jeffman]

If, in a single execution of a single script, you expect to run a significant number of queries that have the same pattern, only different values, prepared statements let PHP optimize the code in advance, resulting in less drain on the system, and faster execution overall.The safety thing JSG mentions is to prevent SQL-injection attacks. Google that if you don't know about it already.(In a nutshell: injection attacks work when you concatenate SQL commands (in string form) with data that is also in string form. The API cannot distinguish logic strings from data stings. (A string is a string.) If a malicious user injects SQL logic strings into your POST data, and then you mix add the data to an SQL command, he can hijack your query. This is an unfortunate consequence of evaluating a command-line language in a procedural environment.)Prepared statements separate the logic from the data, so that the API will parse data as data and NOT mix it into the logic. Regardless of the content, a string will not be interpreted as an SQL command, even if it is written in SQL language.

[Fmdpa]

If, in a single execution of a single script, you expect to run a significant number of queries that have the same pattern, only different values, prepared statements let PHP optimize the code in advance, resulting in less drain on the system, and faster execution overall.

Coincidentally, I just did that earlier today for the first time. It is a great time-saver, for sure!

[wilsonf1]

ok well i get the theory then. I understand what SQL injection is and prepare is a way to prevent it, I just don't get how this code is meant to work!if there's no prepare function in my $db object this will always fall over right? What should I be passing in as $db if not my own object?

[wilsonf1]

i'll add some more details as i really want to find out how to use this prepare lark!!!So here is the function that uses prepare:

  function delete($db) {    if(is_null($this->_Id)) {      return;    }    else {      $query = $db->prepare("DELETE FROM `www_xxx_com_cms` WHERE `Id`=? LIMIT 1;");      $query->execute(array($this->_Id));    }  }

Now what should I be passing in as $db?I have a database class that I use like so:

$db = new dbConnection();$cmsDb= new cmsDb;$cmsDb->delete($db);

But I can't just send through a whole class to delete can I, doesn't make sense? So what do I sent through to that delete function?Really appreciate your help

[Synook]

Don't worry, there's only three options, I'm sure you can try them all . Unless their randomised!

You can pass the instance of dbConnection (i.e., $db) to cmsDb::delete(), there's nothing wrong with that.P.S. you need brackets after new cmsDb:

$cmsDb= new cmsDb();

[wilsonf1]

You can pass the instance of dbConnection (i.e., $db) to cmsDb::delete(), there's nothing wrong with that.P.S. you need brackets after new cmsDb:

$cmsDb= new cmsDb();

I've tried that but I just get this?Fatal error: Call to undefined method dbConnection::prepare() in C:\wamp\www\CMS\objects\db\cms.php on line 407

[Synook]

That's because dbConnection has no prepare() method. If you wrote the class, you'll need to add it in. Alternatively, the script that generated the "loads of functions" may be expecting you to use a different class (for example, mysqli has a prepare() method: http://php.net/manual/en/mysqli.prepare.php).

[wilsonf1]

That's because dbConnection has no prepare() method. If you wrote the class, you'll need to add it in. Alternatively, the script that generated the "loads of functions" may be expecting you to use a different class (for example, mysqli has a prepare() method: http://php.net/manual/en/mysqli.prepare.php).

cool ok so i need a prepare function - do you recommend i write one or is it something i can nab?

[Synook]

Well, technically you should read the documentation on the program that generated the delete etc. functions to find out what it's expecting. Otherwise, you can just use a class appropriate for your database that works, e.g. mysqli. The point of having your own database interface class is to either add additional features, or to abstract the functions so you can support multiple RDBMSs.

[wilsonf1]

Well, technically you should read the documentation on the program that generated the delete etc. functions to find out what it's expecting. Otherwise, you can just use a class appropriate for your database that works, e.g. mysqli. The point of having your own database interface class is to either add additional features, or to abstract the functions so you can support multiple RDBMSs.

Right well i'm slowy getting this!A programmer gave me his small Database class when I said I was learning PHP and i've been fine using it. FRom what I can tell, is mysqli a built in class to PHP that I could use instead of this class my mate gave me?So all database interaction and functions will reference the mysqli class?(just to confirm, nothing needs downloading or installing, just this needs replacing with something?: $db = new dbConnection()

[boen_robot]

No need to download and/or install anything additional, sure, but there may be a need to configure PHP appropriately.Try in a separate PHP file to do something like

<?phperror_reporting(E_ALL);ini_set('display_errors', 1);$db = new mysqli('127.0.0.1', 'root', '', 'test');echo 'OK';?>

And see if there are any error messages. If it says something like "... undefined class mysqli ...", then you'll have to either contact your host (if you aren't doing this on your computer) or (if you are on your own computer) open up your php.ini, find and remove the ";" on the line that says ";extension=php_mysqli.dll", save php.ini of course, and restart Apache.If it says something elese (e.g. "access denied to root@127.0.0.1, PASSWORD NO", "... no such database ...", etc.) you're ready, but you'll have to check the file in which the dbConnection() class is defined, and find out the host, username, password and database name (the arguments to the mysqli constructor above, in the same order).

[wilsonf1]

thanks for that replyI got OK messages back on localhost and my live web server - so i'm good to use it.Is it recommended that I use the mysqli frame work or shall I just stick with my own database object?If i'm learning php, is it fair to say that a lot of code i come across in the years to come could be using mysqli so i may as use use something that the public are using, rather than my own custom object?

[boen_robot]

Is it recommended that I use the mysqli frame work or shall I just stick with my own database object?

Depends... does that database object have a good documentation somewhere? Are you aware of all of its features, or at least have an understanding of the added ones? If the answer to any of those is "yes", then keep using it.If "no", then it's probably a good idea to switch to mysqli for a while, at least until you get the grips with it. You can check out mysqli's documentation to see exactly what you can do, what are the expected results, and even see some code examples. It's possible that your DB class extends mysqli to offer additional functionality, but the base features are likely to be more efficient, so it's a good idea to know which ones are they. Alternatively, your DB class may be a way to abstract away the SQL writing in order to support multiple kinds of databases, and not just MySQL. If that's the case, I'd reccomend switching to a more full featured abstraction class like Zend_Db (you will have to download and install the Zend framework for that one though).Documentation is key - it's OK to use something "custom" as long as you know how to use it. If you wrote it yourself, you'll surely know how, and if someone else wrote it for you, they should provide you with some documentation. Avoid using stuff without knowing what's capable of and how it basically works.

[wilsonf1]

Depends... does that database object have a good documentation somewhere? Are you aware of all of its features, or at least have an understanding of the added ones? If the answer to any of those is "yes", then keep using it.If "no", then it's probably a good idea to switch to mysqli for a while, at least until you get the grips with it. You can check out mysqli's documentation to see exactly what you can do, what are the expected results, and even see some code examples. It's possible that your DB class extends mysqli to offer additional functionality, but the base features are likely to be more efficient, so it's a good idea to know which ones are they. Alternatively, your DB class may be a way to abstract away the SQL writing in order to support multiple kinds of databases, and not just MySQL. If that's the case, I'd reccomend switching to a more full featured abstraction class like Zend_Db (you will have to download and install the Zend framework for that one though).Documentation is key - it's OK to use something "custom" as long as you know how to use it. If you wrote it yourself, you'll surely know how, and if someone else wrote it for you, they should provide you with some documentation. Avoid using stuff without knowing what's capable of and how it basically works.

The object couldn't be more simple, what do you think, stick with it?:

<?phpclass dbConnection {    public $sql;    var $connection;    var $recordset;    var $row_count;    public function __construct() {    }    public function __destruct() {    }			    function connect() {        $this->connection = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);        $connection_string = mysql_select_db(DB_NAME);        mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);        mysql_select_db(DB_NAME);        mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $this->connection);    }    function formatTime($time) {        return str_ireplace(':', '', $time);    }    function formatDate($date) {        return strftime('%G-%m-%d', strtotime($date));    }    function rowCount($sql) {        $this->row_count = mysql_num_rows(mysql_query("$sql"));        return $this->row_count;    }    function row_read($recordset) {        if (!$recordset) {            die('<br><br>Invalid query :<br><br><bold>' . $this->sql . '</bold><br><br>' . mysql_error());        }        $rs = mysql_fetch_array($recordset);        return $rs;    }    function insert($sql) {        $this->sql = $sql;        $result = mysql_query($sql);        if (!$result) {            die('<br><br>Invalid query :<br><br><bold>' . $sql . '</bold><br><br>' . mysql_error());        }    }    function query($sql) {        $this->sql = $sql;        $this->recordset = mysql_query($sql);        if ($this->recordset == false) {            $this->row_count = 0;        } else {            $this->row_count = mysql_num_rows($this->recordset);        }        return $this->recordset;    }    function update($sql) {        $result = mysql_query($sql);        $this->sql = $sql;        if (!$result) {            die('<br><br>Invalid query :<br><br><bold>' . $sql . '</bold><br><br>' . mysql_error());        }    }}?>

[wilsonf1]

the more i look into this, the madder it gets!i tried that delete function and a function called get, using mysqli and although prepare works, fetchAll doesn't and execute is different - so I don't think those functions were coded for mysqli specificallyi'm, just gonna carry on using my my own object as there's hardly anything to it

[boen_robot]

Well, personally, I don't like that custom object for at least 3 reasons:1. It uses mysql instead of mysqli.2. It mixes database dealing with other stuff, like date formatting.3. It provides nothing with its additional methods like insert() and update(). You might as well use query().But if the CMS requires it, so be it. If you're planning to write new apps, I'd highly reccomend ditching this, and learning about MySQLi.