Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

converting raw queries to prepared statement

suppose I have my 1995 fashion function meant to send queries to mysql. I have lots of queries on my project and I'm looking for a function/class able to parse the raw query (suppose: SELECT foo from bar where pizza = 'hot' LIMIT 1) and create a prepared statement with php. do you have any tips on that? is it worth it? or it's better to just rewrite all the queries?

I can count 424 queries on my project, and that's just SELECTs

thanks for any help

like image 462
sathia Avatar asked Nov 05 '22 12:11

sathia


1 Answers

Try this:

function prepare1995Sql_EXAMPLE ($sqlString) {

    # regex pattern
    $patterns = array();
    $patterns[0] = '/\'.*?\'/';

    # best to use question marks for an easy example
    $replacements = array();
    $replacements[0] = '?';

    # perform replace
    $preparedSqlString = preg_replace($patterns, $replacements, $sqlString);

    # grab parameter values
    $pregMatchAllReturnValueHolder = preg_match_all($patterns[0], $sqlString, $grabbedParameterValues);
    $parameterValues = $grabbedParameterValues[0];

    # prepare command:
    echo('$stmt = $pdo->prepare("' . $preparedSqlString . '");');
    echo("\n");

    # binding of parameters
    $bindValueCtr = 1;
    foreach($parameterValues as $key => $value) {
    echo('$stmt->bindParam(' . $bindValueCtr . ", " . $value . ");");
    echo("\n");
    $bindValueCtr++;
    }

    # if you want to add the execute part, simply:
    echo('$stmt->execute();');
}

# TEST!
$sqlString = "SELECT foo FROM bar WHERE name = 'foobar' or nickname = 'fbar'";
prepare1995Sql_EXAMPLE ($sqlString);

Sample output would be:

$stmt = $pdo->prepare("SELECT foo FROM bar WHERE name = ? or nickname = ?");
$stmt->bindParam(1, 'foobar');
$stmt->bindParam(2, 'fbar');
$stmt->execute();

This would probably work if all your sql statements are similar to the example, conditions being strings. However, once you require equating to integers, the pattern must be changed. This is what I can do for now.. I know it's not the best approach at all, but for a sample's sake, give it a try :)

like image 120
Nonym Avatar answered Nov 09 '22 06:11

Nonym