Convert mysql functions to pdo

Question

How can I convert my MySQL functions to PDO? I have successfully connected to my DB with the PDO call, but my scripts are still using the MySQL functions.

function query_basic($query)
{
    $result = mysql_query($query);
    if ($result == FALSE)
    {
        $msg = 'Invalid query : '.mysql_error()."\n";
        echo $msg;
    }
}

function query_numrows($query)
{
    $result = mysql_query($query);
    if ($result == FALSE)
    {
        $msg = 'Invalid query : '.mysql_error()."\n";
        echo $msg;
    }
    return (mysql_num_rows($result));
}

function query_fetch_assoc($query)
{
    $result = mysql_query($query);
    if ($result == FALSE)
    {
        $msg = 'Invalid query : '.mysql_error()."\n";
        echo $msg;
    }
    return (mysql_fetch_assoc($result));
}

[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [pdo](https://wiki.php.net/rfc/mysql_deprecation) or [mysqli](http://stackoverflow.com/questions/tagged/mysqli). — zessx, Oct 18 '13 at 20:57
These errors not because of real escape. There's an error with your MySQL connection to the MySQL server. — Ali Demirci, Oct 18 '13 at 21:30
As Ali Demirci said, there has to be a MySQL connection to use this function. — user2577405, Oct 18 '13 at 21:51
Did you read any of the past similar questions? Look to the right, under the heading **Related**, there are quite a few. — Bill Karwin, Oct 18 '13 at 23:17

score 3 · Answer 1 · edited Oct 18 '13 at 20:56

3

You forgot a semicolon after:

$clientid = mysql_real_escape_string($_GET['id'])

Correct:

$clientid = mysql_real_escape_string($_GET['id']);
                                                 ^

edited Oct 18 '13 at 20:56

Amal Murali

75,622
18
128
150

answered Oct 18 '13 at 20:54

Cristian Bitoi

1,557
1
10
14

Hi. I saw that as an option and already tried it earlier.. maybe i messed something up. But, I added the semicolon and now i get the "else/exit" error in the php "Error, clientid invalid". I have absolotely no idea how this doesnt work now? If I remove "mysql_real_escape_string".. everything works fine... if i add "mysql_real_escape_string" ... i get the error? – user2896335 Oct 18 '13 at 21:02
Impossible, because `mysql_real_escape_string` is inside first if, and you say that you get else/exit error, that is in the second condition. – Cristian Bitoi Oct 18 '13 at 21:06
Right. Now comes the error log :p. [Yes the mysql db is on a separate server]. ERROR1 PHP Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2). ERROR2 PHP Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established. My mind is stuck, questioning myself.. how it wants to establish a connection to a local socket? what? – user2896335 Oct 18 '13 at 21:15
`$db = new PDO('mysql:host=myhost;dbname=mydb', 'login', 'password'));` http://www.php.net/manual/en/pdo.construct.php – Cristian Bitoi Oct 18 '13 at 21:27

score 1 · Answer 2 · answered Oct 18 '13 at 21:45

The cause of these errors is that you're calling mysql_real_escape_string() before you've opened the connection to the database.

mysql_real_escape_string() needs to be called after the DB connection has been established because it needs to know what the encoding scheme is for the database it's escaping for, so it knows what characters need to be escaped.

Ideally, you should call it immediately before creating the SQL string; escaped strings should be used only in the context that they have been escaped for, so you don't want them hanging around the program too far away from where the queries are built. (This applies to all kinds of escaping, not just SQL).

Ultimately, you would be much better off if you switch to using a more modern database API such as PDO. You still need to take care to avoid injection attacks, no matter what DB API you're using, but it's a lot easier with PDO.

A good tutorial for PDO can be found here: http://www.sitepoint.com/avoid-the-original-mysql-extension-2/

Hope that helps.

Convert mysql functions to pdo

2 Answers2